表名作为变量 [英] Table name as variable
问题描述
我正在尝试执行此查询:
I am trying to execute this query:
declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename
这会产生以下错误:
信息1087,第16级,状态1,第5行
Msg 1087, Level 16, State 1, Line 5
必须声明表变量"@tablename".
Must declare the table variable "@tablename".
动态填充表名的正确方法是什么?
What's the right way to have table name populated dynamically?
推荐答案
对于静态查询(如您的问题中的查询),表名和列名必须是静态的.
For static queries, like the one in your question, table names and column names need to be static.
对于动态查询,您应该动态生成完整的SQL,并使用sp_executesql来执行它.
For dynamic queries you should generate the full SQL dynamically, and use sp_executesql to execute it.
这是一个脚本示例,用于比较不同数据库的相同表之间的数据:
Here is an example of a script used to compare data between the same tables of different databases:
静态查询:
SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]
因为我要轻松更改table
和schema
的名称,所以创建了此动态查询:
since I want easily change tha name of table
and schema
I have created this dynamic query:
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
这篇关于表名作为变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!