SQL 声明变量 [英] SQL Declare Variables

问题描述

谁能查看我的声明...

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)

我的错误是...

消息 1087，级别 15，状态 2，第 1 行
必须声明表变量@tblName".

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@tblName".

推荐答案

你的 @tblName 属性存在于外部作用域——普通"代码行的作用域——但不存在于内部作用域您在那里的字符串中构建的 SQL....

Your @tblName property 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....

您需要更改您的行以阅读:

You need to change your lines to read:

SET @strSQL = 'INSERT INTO ' + @tblName + ' VALUES(''trylng'', ''1'')'

然后它应该可以正常工作.

and then it should work just fine.

此外，您没有提到您的 SQL Server 版本 - 但从 SQL Server 2005 或更新版本开始，您应该停止使用 sysobjects 和 sysindexes - 而是使用新的 sys 架构包含或多或少相同的信息 - 但更容易获得.将您的查询更改为:

Also, you're not mentioning your SQL Server version - but as of SQL Server 2005 or newer, you should stop using sysobjects and sysindexes - instead, use the new sys schema that contains more or less the same information - but more easily available. Change your query to:

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%'''  

请参阅 MSDN:查询 SQL Server 系统目录，了解更多信息有关新 sys 架构中的可用内容以及如何充分利用它的更多信息！

See MSDN: Querying the SQL Server System Catalog for a lot more information on what's available in the new sys schema and how to make the most of it!

正如 "rsbarro" 指出的那样:将这里的 SQL 语句放在引号中很奇怪 - 您是否也在使用 EXEC(...) 执行此语句??但是，您如何将值分配回 @tblName 属性?真的没有意义.....

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 @tblName property? Doesn't really make sense.....

如果你想实际运行这个查询来获取一个值，你应该有这样的东西:

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%'

您需要有一个 TOP 1 才能确定只获得一个值 - 否则此语句可能会失败(如果选择了多行).

You need to have a TOP 1 in there to be sure to get just a single value - otherwise this statement could fail (if multiple rows are selected).

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