Declaring SQL variables - SQL Server

Question

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

What I want to do is get the table name on the variable @tblName and insert some data in @strSQL variable

For example... the result in @tblName is CustomerInfo

then in @strSQL I will going to use the result in @tblName as my table name in my Insert Command.

So the @strSQL variable will be;

INSERT INTO CustomerInfo VALUES(......)

Answer 1

Try this from my answer to your other question:

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

 SET @strSQL = 'INSERT INTO ' + @tblName  + ' VALUES(''trylng'', ''1'')'
 EXEC (@strSQL)

You're still not mentioning the SQL Server version you're using. 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.

See [MSDN: Querying the SQL Server System Catalog][1] for a lot more information on what's available in the new sys schema and how to make the most of it!