SQL 表名作为变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2838490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Table name as variable
提问by SoftwareGeek
I am trying to execute this query:
我正在尝试执行此查询：
declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename
This produces the following error:
这会产生以下错误：
Msg 1087, Level 16, State 1, Line 5
Must declare the table variable "@tablename".
消息 1087，级别 16，状态 1，第 5 行
必须声明表变量“@tablename”。
What's the right way to have table name populated dynamically?
动态填充表名的正确方法是什么？
采纳答案by mdma
For static queries, like the one in your question, table names and column names need to be static.
对于静态查询，如您问题中的查询，表名和列名需要是静态的。
For dynamic queries you should generate the full SQL dynamically, and use sp_executesql to execute it.
对于动态查询，您应该动态生成完整的 SQL，并使用 sp_executesql 来执行它。
Here is an example of a script used to compare data between the same tables of different databases:
以下是用于比较不同数据库的相同表之间数据的脚本示例：
static query:
静态查询：
SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]
since I want easily change tha name of tableand schemaI have created this dynamic query:
因为我想轻松更改它的名称table并且schema我创建了这个动态查询：
declare @schema varchar(50)
declare @table varchar(50)
declare @query nvarchar(500)
set @schema = 'dbo'
set @table = 'ACTY'
set @query = 'SELECT * FROM [DB_ONE].['+ @schema +'].[' + @table + '] EXCEPT SELECT * FROM [DB_TWO].['+ @schema +'].[' + @table + ']'
EXEC sp_executesql @query
Since dynamic queries have many details that need to be considered and they are hard to mantain I recommend that you read : The curse and blessings of dynamic SQL
由于动态查询需要考虑的细节很多，而且很难维护，我建议您阅读：动态SQL的诅咒和祝福
回答by Tim
Change your last statement to this:
将您的最后一条语句更改为：
EXEC('SELECT * FROM ' + @tablename)
This is how I do mine in a Stored Procedure. The first block will declare the variable, and set the table name based on the current year and month name, in this case TEST_2012OCTOBER. I then check if it exists in the DB already, and remove if it does. Then the next block will use a SELECT INTO Statement to create the table and populate it with records from another table with parameters.
这就是我在存储过程中的做法。第一个块将声明变量，并根据当前年份和月份名称设置表名称，在本例中为 TEST_2012OCTOBER。然后我检查它是否已经存在于数据库中，如果存在则将其删除。然后下一个块将使用 SELECT INTO 语句创建表并用来自另一个表的带参数的记录填充它。
--DECLARE TABLE NAME VARIABLE DYNAMICALLY
DECLARE @table_name varchar(max)
SET @table_name =
(SELECT 'TEST_'
+ DATENAME(YEAR,GETDATE())
+ UPPER(DATENAME(MONTH,GETDATE())) )
--DROP THE TABLE IF IT ALREADY EXISTS
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = @table_name AND xtype = 'U')
BEGIN
EXEC('drop table ' + @table_name)
END
--CREATES TABLE FROM DYNAMIC VARIABLE AND INSERTS ROWS FROM ANOTHER TABLE
EXEC('SELECT * INTO ' + @table_name + ' FROM dbo.MASTER WHERE STATUS_CD = ''A''')
回答by Atul Chaudhary
Bit late for an answer but should help out someone else:
回答有点晚，但应该帮助其他人：
CREATE PROCEDURE [dbo].[GetByName]
@TableName NVARCHAR(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sSQL nvarchar(500);
SELECT @sSQL = N'SELECT * FROM' + QUOTENAME(@TableName);
EXEC sp_executesql @sSQL
END
回答by dcp
You can't use a table name for a variable, you'd have to do this instead:
您不能为变量使用表名，您必须这样做：
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from yourtable'
EXEC (@sqlCommand)
回答by Paul Kearney - pk
You'll need to generate the sql dynamically:
您需要动态生成 sql：
declare @tablename varchar(50)
set @tablename = 'test'
declare @sql varchar(500)
set @sql = 'select * from ' + @tablename
exec (@sql)
回答by ghgh
Use sp_executesqlto execute any SQL, e.g.
使用sp_executesql执行任何SQL，如
DECLARE @tbl sysname,
@sql nvarchar(4000),
@params nvarchar(4000),
@count int
DECLARE tblcur CURSOR STATIC LOCAL FOR
SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
ORDER BY 1
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @tbl
IF @@fetch_status <> 0
BREAK
SELECT @sql =
N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
N' WHERE LastUpdated BETWEEN @fromdate AND ' +
N' coalesce(@todate, ''99991231'')'
SELECT @params = N'@fromdate datetime, ' +
N'@todate datetime = NULL, ' +
N'@cnt int OUTPUT'
EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT
PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END
DEALLOCATE tblcur
回答by Disha Sojitra
Also, You can use this...
此外，您可以使用此...
DECLARE @SeqID varchar(150);
DECLARE @TableName varchar(150);
SET @TableName = (Select TableName from Table);
SET @SeqID = 'SELECT NEXT VALUE FOR '+ @TableName + '_Data'
exec (@SeqID)
回答by user13581111
Declare @tablename varchar(50)
set @tablename = 'Your table Name'
EXEC('select * from ' + @tablename)
回答by Reza Jenabi
you need to use the SQL Server dynamic SQL
您需要使用 SQL Server 动态 SQL
DECLARE @table NVARCHAR(128),
@sql NVARCHAR(MAX);
SET @table = N'tableName';
SET @sql = N'SELECT * FROM ' + @table;
Use EXECto execute any SQL
使用EXEC执行任何 SQL
EXEC (@sql)
Use EXEC sp_executesqlto execute any SQL
使用EXEC sp_executesql执行任何 SQL
EXEC sp_executesql @sql;
Use EXECUTE sp_executesqlto execute any SQL
使用EXECUTE sp_executesql执行任何 SQL
EXECUTE sp_executesql @sql
回答by Laurent
Declare @fs_e int, @C_Tables CURSOR, @Table varchar(50)
SET @C_Tables = CURSOR FOR
select name from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 AND name like 'TR_%'
OPEN @C_Tables
FETCH @C_Tables INTO @Table
SELECT @fs_e = sdec.fetch_Status FROM sys.dm_exec_cursors(0) as sdec where sdec.name = '@C_Tables'
WHILE ( @fs_e <> -1)
BEGIN
exec('Select * from '+ @Table)
FETCH @C_Tables INTO @Table
SELECT @fs_e = sdec.fetch_Status FROM sys.dm_exec_cursors(0) as sdec where sdec.name = '@C_Tables'
END
