Declaring SQL variables - SQL Server

sql-server sql-server-2008 variables declare
Argel Joseph picture Argel Joseph · Feb 23, 2012 · Viewed 91k times · Source

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

colithium picture colithium · Feb 23, 2012

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)

Related questions

SQL NVARCHAR and VARCHAR Limits

All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that there is a 4000 max set for NVARCHAR(…

Read More
Variables scope which are defined within a while block in stored procedures - SQl Server

I've come across a interesting scenario (at least for me) in a stored procedure. Would like to have experts opinion and thoughts on it. DECLARE @loopcounter INT SET @loopcounter=10 WHILE @loopcounter > 0 BEGIN DECLARE @insidevalue int IF (@loopcounter%2 = 0) SET @insidevalue = @…

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More