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

Solution 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!

Solution 2

When you declare more than one variable with a single DECLARE statement, you only put the type once (at the end):

DECLARE @tblName, @strSQL varchar(MAX)

Solution 3

This should be something can really run:

DECLARE @tblName varchar(MAX), 
        @strSQL varchar(MAX)

SET @tblName =  (SELECT DISTINCT TOP 1  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)

Anything in quote means that's a string and don't expect sql server run it as statement, same thing to the variable in a string, you can't quote it

Comments

[marc_s]

possible duplicate of SQL Declare Variables

[gbjbaanb]

Possible duplicate of SQL Declare Variables

[marc_s]

DSINTINCT and TOP 1 is a bit too much..... if you only have 1 result, it cannot possible have duplicates anyway.....

[colithium]

I could have sworn the original question was about MySql (which is what I answered). It appears Sql Server allows duplicate type definitions in DECLARE statements

[Simon Wang]

Just want to make sure it won't crash as there might be some different table have similar name

[Argel Joseph]

oops, my bad,, I'm using SQL Server 2008, nway..thanks 4 d help

[Argel Joseph]

another thing...what if i am going to use an IF Statement in @strSQL before the insert Command because i want to verify first if the values that i'm going to insert in not existing in the table. sorry for many questions...coz i'm only starting to study the sql.

[Shawn Kovac]

This answer is incorrect. SQL Server does NOT allow DECLARE @tblName, @strSQL varchar(MAX). One must specify the type of each variable like: declare @i int, @t varchar(max);