SQL Declare Variables
Solution 1
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.
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%'''
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).
Solution 2
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 @strSQL
) when @tblName
will always have the same value (since there are no variables used in the WHERE
clause).
Argel Joseph
Updated on February 23, 2020Comments
-
Argel Joseph over 4 years
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". -
rsbarro over 12 yearsAm I missing something? Why is @tblName wrapped in single quotes? Won't that just set it equal to the string, and not the table name?
-
rsbarro over 12 yearsNo, I mean the second statement. Shouldn't it read
SET @tblName = SELECT DISTINCT t.name...
without quotes? The way it is written @strSQL will have a value ofINSERT INTO SELECT DISTINCT t.Name as TableName...
. Seems to me that will be invalid, no? -
marc_s over 12 years@rsbarro: ah ok - well, not sure - I was assuming the OP would be executing that SQL statement using
EXEC(....)
as well. If not - then you're absolutely right, it won't work! -
rsbarro over 12 yearsCool. It's late where I'm at, and I thought I was either seeing some new syntax or losing my mind... =]
-
Andriy M over 12 years+1, only one note: generally it is safer to incorporate names into dynamic scripts like this:
'INSERT INTO ' + QUOTENAME(@tblName) + '…'
, rather than simply to put square brackets around them. -
Argel Joseph over 12 yearsAnother question...why do we need to use join to SYS.INDEXES since on the list of tables that match to our "WHERE" are in the SYS.TABLES..??
-
marc_s over 12 years@ArgelJoseph: I don't know why you're using
sys.indexes
- that was your statement, I just tried to make it work. But you're right - thesys.indexes
seems unneeded here in that statement....