表名作为变量 [英] 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屋！