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…

  • How do I stop Database deploy from removing Server Triggers?
  • SQL Update Parent Table Field based on Multiple Child Table Rows
  • ADD time 23:59:59.999 to end date for between
  • Using the HAVING Clause with GROUP by to Return Unique Records
  • SQL Server GUID sort algorithm. Why?
  • Logging intermediary results in stored procedures in SQL Server
  • Msg 1087, Level 15, State 2, Line 1
    Must declare the table variable “@tblName”.

    2 Solutions collect form web for “SQL Declare Variables”

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

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.