問題描述

誰能查看我的聲明...

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 聲明變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！