問題描述

誰能查看我的聲明...

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)

我的錯(cuò)誤是...

消息 1087，級(jí)別 15，狀態(tài) 2，第 1 行
必須聲明表變量@tblName".

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

推薦答案

你的 @tblName 屬性存在于外部作用域——普通"代碼行的作用域——但不存在于內(nèi)部作用域您在那里的字符串中構(gòu)建的 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'')'

然后它應(yīng)該可以正常工作.

and then it should work just fine.

此外，您沒有提到您的 SQL Server 版本 - 但從 SQL Server 2005 或更新版本開始，您應(yīng)該停止使用 sysobjects 和 sysindexes - 而是使用新的 sys 架構(gòu)包含或多或少相同的信息 - 但更容易獲得.將您的查詢更改為:

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

請(qǐng)參閱 MSDN:查詢 SQL Server 系統(tǒng)目錄，了解更多信息有關(guān)新 sys 架構(gòu)中的可用內(nèi)容以及如何充分利用它的更多信息！

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 語句放在引號(hào)中很奇怪 - 您是否也在使用 EXEC(...) 執(zhí)行此語句??但是，您如何將值分配回 @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.....

如果你想實(shí)際運(yùn)行這個(gè)查詢來獲取一個(gè)值，你應(yīng)該有這樣的東西:

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

您需要有一個(gè) TOP 1 才能確定只獲得一個(gè)值 - 否則此語句可能會(huì)失敗(如果選擇了多行).

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).

這篇關(guān)于SQL 聲明變量的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！