SQL 声明变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9407703/
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
SQL Declare Variables
提问by Argel Joseph
Can anyone check on my statement...
谁能看看我的发言...
DECLARE @tblName varchar(MAX),
@strSQL varchar(MAX)
SET @tblName ='SELECT DISTINCT o.name as TableName
FROM sysobjects o
JOIN sysindexes x on o.id = x.id
WHERE o.name LIKE ''%empty%'''
SET @strSQL = 'INSERT INTO @tblName VALUES(''trylng'', ''1'')'
EXEC (@strSQL)
my error is...
我的错误是...
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@tblName".
消息 1087，级别 15，状态 2，第 1 行
必须声明表变量“@tblName”。
回答by marc_s
Your @tblNameproperty exists at the outer scope - the scope of your "normal" code lines - but not at the inner scope of the SQL you're constructing in the string there....
您的@tblName属性存在于外部范围 - 您的“正常”代码行的范围 - 但不在您在那里的字符串中构建的 SQL 的内部范围......
You need to change your lines to read:
您需要更改您的行以阅读：
SET @strSQL = 'INSERT INTO ' + @tblName + ' VALUES(''trylng'', ''1'')'
and then it should work just fine.
然后它应该可以正常工作。
Also, you're not mentioning your SQL Server version - but as of SQL Server 2005 or newer, you should stop using sysobjectsand sysindexes- instead, use the new sysschema that contains more or less the same information - but more easily available. Change your query to:
此外，您没有提到您的 SQL Server 版本 - 但从 SQL Server 2005 或更高版本开始，您应该停止使用sysobjects，sysindexes而是使用sys包含或多或少相同信息的新架构 - 但更容易获得。将您的查询更改为：
SET @tblName ='SELECT DISTINCT t.name as TableName
FROM sys.tables t
INNER JOIN sys.indexes i on i.object_id = t.object_id
WHERE t.name LIKE ''%empty%'''
See MSDN: Querying the SQL Server System Catalogfor a lot more information on what's available in the new sysschema and how to make the most of it!
有关新架构中的可用内容以及如何充分利用它的更多信息，请参阅MSDN：查询 SQL Server 系统目录sys！
As "rsbarro" pointed out : putting this SQL statement here into quotes is odd - are you executing this statement using EXEC(...), too?? But then how do you assign the value back to the @tblNameproperty? Doesn't really make sense.....
正如“rsbarro”指出的那样：将这里的 SQL 语句放在引号中很奇怪 - 您是否EXEC(...)也在使用 执行此语句？？但是，您如何将值分配回@tblName属性？真没意思……
If you want to actually run this query to get a value, you should have something like this:
如果你想实际运行这个查询来获取一个值，你应该有这样的东西：
SELECT TOP 1 @tblName = t.name
FROM sys.tables t
INNER JOIN sys.indexes i on i.object_id = t.object_id
WHERE t.name LIKE '%empty%'
You need to have a TOP 1in there to be sureto get just a single value - otherwise this statement could fail (if multiple rows are selected).
你需要TOP 1在那里有一个确保只得到一个值 - 否则这个语句可能会失败（如果选择了多行）。
回答by rsbarro
Not sure exactly what you're trying to do, but I think you want something like this:
不确定你想要做什么，但我认为你想要这样的东西：
DECLARE @tblName varchar(MAX), @strSQL varchar(MAX)
SET @tblName =
(select distinct o.name as TableName
from sysobjects o
join sysindexes x on o.id = x.id
where o.name LIKE '%empty%')
SET @strSQL = 'INSERT INTO [' + @tblName + '] VALUES(''trylng'', ''1'')'
exec (@strSQL)
That being said, there are still a couple things to watch out for here. You need to handle the condition where the SELECT DISTINCTreturns anything other than a single record. Also, I don't really understand the need to build dynamic SQL (in @strSQL) when @tblNamewill always have the same value (since there are no variables used in the WHEREclause).
话虽如此，这里仍有一些事情需要注意。您需要处理SELECT DISTINCT返回除单个记录以外的任何内容的情况。另外，我真的不明白需要构建动态 SQL (in @strSQL) when@tblName总是具有相同的值（因为WHERE子句中没有使用变量）。
