CODEWITHSUNDEEP
sql-serversql-server-2008variablesdeclare
Argel Joseph
Argel Joseph

Reputation: 275

Declaring SQL variables - SQL Server

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

Upvotes: 7

Views: 95198

Answers (3)

marc_s
marc_s

Reputation: 754938

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!

Upvotes: 2

Simon Wang
Simon Wang

Reputation: 2943

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

Upvotes: 1

colithium
colithium

Reputation: 10327

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)

Upvotes: 2

Related Questions