declare @tablename varchar(20)
select @tablename = tablename from A where id = 1
exec('select * from ' + @tablename + ' where id = 1')
select @tablename = tablename from A where id = 1
exec('select * from ' + @tablename + ' where id = 1')
select @tablename=tablename from aexec('select * from '+@tablename+' where id=1')
Select * from (select tablename from A where id = 1) temp where id = 1
这个例子显示 EXECUTE 语句如何处理动态生成的、含有变量的字符串。这个例子创建 tables_cursor 游标来保存所有用户定义表 (type = U) 的列表。说明 此例子只用作举例。
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/* A @@FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor
as
declare @id int
declare @tablename varchar(50)
select @id=id,@tablename=tablename from A where 条件
exec ('select * from '+@tablename+' where id='+@id)
select @sql='select * from ['+tablename+'] where id=1'
from A表 where id=1exec(@sql)
select @tablename = tablename from A where id = 1
exec('select * from ' + @tablename + ' where id = 1')
表a:
id tablename
1 b
2 c
3 d表b:
id name
1 china表c:
id name
2 usa表d:
id name
3 tw表b,c,d中的id字段与表a是关联的,现在我要列出这样的结果:id name
1 china
2 usa
3 tw
union all
select id,(select name from c where id=a.id) from a where tablename='c'
union all
select id,(select name from d where id=a.id) from a where tablename='d'
union all
select a.id,c.name from a,c where a.tablename='c' and a.id=c.id
union all
select a.id,d.name from a,d where a.tablename='d' and a.id=d.id
union
select id,(select name from c where id=a.id) from a where tablename='c'
union
select id,(select name from d where id=a.id) from a where tablename='d'当表a中的tablename字段为b,c,d以外的字符就不用管了!
select id,(select name from b where id=a.id) from a where tablename='b'
union
select id,(select name from c where id=a.id) from a where tablename='c'
union
select id,(select name from d where id=a.id) from a where tablename='d'
union
select id,(select name from d where id=a.id) from a where tablename='e'
union
select id,(select name from d where id=a.id) from a where tablename='f'
union
select id,(select name from d where id=a.id) from a where tablename='g'
union
select id,(select name from d where id=a.id) from a where tablename='h'。等等效率会不会太低啦?我想象中好像是的。不知究竟如何?
关键是每次不一定是b表,也可能是c,d,e表等,由A表中的tablename字段决定。select b.* from b,(select tabelname from a) as aa,
)
where a.id=b.id and a.id=1;