sql - 在 SQL 中使用记录作为表名

Question

我有一个表，里面有表名。我想运行一个查询来返回它们，然后将它们用作另一个查询的表名。

select tablenames 
from tablelist

结果:

tablenames
----------
table1
table2
table3

然后我想在查询中一起使用这些表名:

DECLARE @table_name varchar(max)

SET @table_name = (SELECT tablenames from tablelist)

EXEC('SELECT * FROM  ' + @table_name)

我收到以下错误:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我希望它做的是类似的事情:

SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM table3

Answer 1

使用一点动态SQL

示例

Declare @SQL varchar(max) = ''
Select @SQL = @SQL+'Select * From '+quotename(tablenames)+';'+char(13)
  From tablelist
--Print @SQL
Exec(@SQL)

生成的 SQL 看起来像这样

Select * From [table1];
Select * From [table2];
Select * From [table3];