blocks|key|3118370|text|我不确定你到底想做什么，但我认为你想要这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3118371|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+'%25empty%25')
SET+@strSQL+=+'INSERT+INTO+['+%2B+@tblName+%2B+']+VALUES(''trylng'',+''1'')'
exec+(@strSQL)|code-block|syntax|javascript|3118372|话虽如此，这里仍然有几件事需要注意。您需要处理SELECT+DISTINCT返回单个记录以外的任何内容的情况。而且，当@tblName总是具有相同的值时(因为WHERE子句中没有使用变量)，我真的不理解构建动态SQL+(在@strSQL中)的必要性。|offset|length|style|CODE|3118373|entityMap^0|0|0|N|F|1N|8|27|5|33|7|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|N|$]]

Not sure exactly what you're trying to do, but I think you want something like this:

<pre><code>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)
</code></pre>

That being said, there are still a couple things to watch out for here. You need to handle the condition where the <code>SELECT DISTINCT</code> returns anything other than a single record. Also, I don't really understand the need to build dynamic SQL (in <code>@strSQL</code>) when <code>@tblName</code> will always have the same value (since there are no variables used in the <code>WHERE</code> clause).

blocks|key|235103|text|你的@tblName属性存在于外部作用域--你的“普通”代码行的作用域--而不是你在那里的字符串中构造的SQL的内部作用域。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|235104|您需要将您的代码行改为：|235105|SET+@strSQL+=+'INSERT+INTO+'+%2B+@tblName+%2B+'+VALUES(''trylng'',+''1'')'|code-block|syntax|javascript|235106|然后它应该会工作得很好。|235107|此外，您没有提到您的SQL+Server版本-但是从SQL+Server2005或更高版本开始，您应该停止使用sysobjects和sysindexes+-而是使用包含或多或少相同信息的新sys架构-但更容易获得。将您的查询更改为：|235108|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+''%25empty%25'''++|235109|有关新sys模式中可用内容以及如何充分利用它的更多信息，请参阅MSDN:+Querying+the+SQL+Server+System+Catalog！|235110|正如"rsbarro“所指出的:将这条SQL语句放在引号中是很奇怪的--您也在使用EXEC(...)执行这条语句吗？但是，如何将值重新分配给@tblName属性呢？这没有什么意义。|235111|如果您想要实际运行此查询来获得一个值，则应该具有如下内容：|235112|+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+'%25empty%25'|235113|您需要有一个TOP+1来确保只获得一个值-否则这个语句可能会失败(如果选择了多个行)。|235114|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189082.aspx^0|2|8|0|0|0|0|1J|A|1U|A|2M|3|0|0|3|3|V|18|0|0|15|9|1Y|8|0|0|0|6|5|0^^$0|@$1|2|3|4|5|6|7|1A|8|@$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1D|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1E|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1F|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|1G|8|@$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]|$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|J|7|1N|8|@]|D|@]|E|$K|L]]|$1|S|3|T|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@$9|1R|A|1S|1|1T]]|E|$]]|$1|U|3|V|5|6|7|1U|8|@$9|1V|A|1W|B|C]|$9|1X|A|1Y|B|C]]|D|@]|E|$]]|$1|W|3|X|5|6|7|1Z|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|J|7|20|8|@]|D|@]|E|$K|L]]|$1|10|3|11|5|6|7|21|8|@$9|22|A|23|B|C]]|D|@]|E|$]]|$1|12|3|-4|5|6|7|24|8|@]|D|@]|E|$]]]|13|$14|$5|15|16|17|E|$18|19]]]]

Your <code>@tblName</code> 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:

<pre><code>SET @strSQL = 'INSERT INTO ' + @tblName + ' VALUES(''trylng'', ''1'')'
</code></pre>

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 <code>sysobjects</code> and <code>sysindexes</code> - instead, use the new <code>sys</code> schema that contains more or less the same information - but more easily available. Change your query to:

<pre><code>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%''' 
</code></pre>

See <a href="http://msdn.microsoft.com/en-us/library/ms189082.aspx" rel="noreferrer">MSDN: Querying the SQL Server System Catalog</a> for a lot more information on what's available in the new <code>sys</code> schema and how to make the most of it!

As "rsbarro" pointed out : putting this SQL statement here into quotes is odd - are you executing this statement using <code>EXEC(...)</code>, too?? But then how do you assign the value back to the <code>@tblName</code> property? Doesn't really make sense.....

If you want to actually run this query to get a value, you should have something like this:

<pre><code> 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%'
</code></pre>

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

Can anyone check on my statement...

<pre><code>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)
</code></pre>

my error is...

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

SQL Declare Variables

SQL Server

有人能检查一下我的陈述吗..。DECLARE @tblName varchar(MAX),         @strSQL varchar(MAX)SET @tblName ='SELECT DISTINCT o.name as TableName                  FROM sysobjects o ...

问SQL声明变量
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL声明变量EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL声明变量
EN