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)
my error is…
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@tblName”.
2 Solutions collect form web for “SQL Declare Variables”
@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.
Also, you’re not mentioning your SQL Server version – but as of SQL Server 2005 or newer, you should stop using
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%'''
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!
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%'
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).
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 DISTINCT returns anything other than a single record. Also, I don’t really understand the need to build dynamic SQL (in
@tblName will always have the same value (since there are no variables used in the