我正试图执行这个查询:
declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename
这会产生以下错误:
Msg 1087, Level 16, State 1, Line 5
必须声明表变量“@tablename”。
有什么正确的方法可以动态地填充表名?
我正试图执行这个查询:
declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename
这会产生以下错误:
Msg 1087, Level 16, State 1, Line 5
必须声明表变量“@tablename”。
有什么正确的方法可以动态地填充表名?
对于静态查询,比如你问题中的那个,表名和列名需要是静态的。
对于动态查询,应该动态生成完整的SQL,并使用sp_executesql来执行它。
这里是一个用于比较不同数据库中相同表格数据的脚本示例:
静态查询:
SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]
因为我希望能轻松更改表(table)
和模式(schema)
的名称,所以我创建了这个动态查询:
declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);
set @schema = 'dbo'
set @table = 'ACTY'
set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
EXCEPT
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);
EXEC sp_executesql @query
由于动态查询需要考虑许多细节并且难以维护,我建议您阅读:动态SQL的祝福与诅咒
将您的最后一条语句更改为以下内容:
EXEC('SELECT * FROM ' + @tablename)
这是我在存储过程中的做法。第一个块将声明变量,并根据当前年份和月份设置表名,例如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''')
EXEC('SELECT * FROM ' + @tablename)
是一个非常糟糕的想法,因为它很容易受到 SQL 注入攻击。 - Aaron Bertrand使用:
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
COUNT(*)
? - Suncatcher您不能将表名用作变量名称。取而代之,您需要执行以下操作:
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from yourtable'
EXEC (@sqlCommand)
你需要动态生成SQL内容:
declare @tablename varchar(50)
set @tablename = 'test'
declare @sql varchar(500)
set @sql = 'select * from ' + @tablename
exec (@sql)
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
DECLARE @table NVARCHAR(128),
@sql NVARCHAR(MAX);
SET @table = N'tableName';
SET @sql = N'SELECT * FROM ' + @table;
使用EXEC执行任何SQL语句:
EXEC (@sql)
使用EXEC sp_executesql执行任何SQL语句:
EXEC sp_executesql @sql;
EXECUTE sp_executesql @sql
Declare @tablename varchar(50)
set @tablename = 'Your table Name'
EXEC('select * from ' + @tablename)
另外,您可以使用这个...
DECLARE @SeqID varchar(150);
DECLARE @TableName varchar(150);
SET @TableName = (Select TableName from Table);
SET @SeqID = 'SELECT NEXT VALUE FOR ' + @TableName + '_Data'
exec (@SeqID)
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
QuoteName()
来避免出现奇怪的名称问题,例如带有空格的New Table
或保留字From
。 - HABO