表名作为变量 [英] A table name as a 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 the 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 to easily change the 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

由于动态查询需要考虑的细节很多，维护难度大，推荐阅读:动态SQL的祸与福

Since dynamic queries have many details that need to be considered and they are hard to maintain, I recommend that you read: The curse and blessings of dynamic SQL

这篇关于表名作为变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！