表名为变量的查询，多个动态表UNIONSELECT top 1000 a.requestid,a.workflowid,a.status,a.creater,a.reque
表名为变量的查询，多个动态表UNION
SELECT top 1000 a.requestid
,a.workflowid
,a.status
,a.creater
,a.requestmark
,c.tablename,c.id
,(select sqr from 'c.tablename' where requestid=a.requestid)
FROM workflow_requestbase a inner join workflow_base AS b on a.workflowid=b.id
inner join workflow_bill AS c on b.formid=c.id
where c.id<0
各位大侠，数据库中有N个表，表名是存储在 c表中的tablename列，而每个表中均有request，sqr 两个字段
我想问的是如何将这个N个表的表名作为form 的参数。
或者是如何将这N个表UNION ALL 起来组成一个临时表，进行使用？ SQL
[解决办法]--再試試以下：
IF object_id('tempdb..#ta') is not null
DROP table #ta;
IF object_id('tempdb..#tb') is not null
DROP table #tb;
create table #ta (request char(10),sqr char(10))
create table #tb (table_yn bit,field_yn bit)
insert into #tb select 0,0
declare @i int,@sql varchar(max),@name varchar(max)
set @i=1
while @i<=200
begin
set @name='formtable_main_'+rtrim(@i)
set @sql='update #tb set table_yn=case when exists (select id from sysobjects where id = object_id('''+@name+''')) then 1 else 0 end'
exec(@sql)
if (select table_yn from #tb)=1
begin
set @sql='update #tb set field_yn=case when exists ('+
'select * from syscolumns where name = ''sqr'' and id='+
'(select id from sysobjects where id = object_id('''+@name+'''))) then 1 else 0 end'
exec(@sql)
set @sql='insert into #ta '+
'select request,'+case when (select field_yn from #tb)=1 then 'sqr' else ''''' sqr' end+
' from '+@name
exec(@sql)
end
set @i=@i+1
end
select * from #ta
