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

possible duplicate of SQL Declare Variables

marc_s
– marc_s

2012-02-23 06:15:19 +00:00
Commented Feb 23, 2012 at 6:15 — marc_s
– marc_s, Commented Feb 23, 2012 at 6:15
Possible duplicate of SQL Declare Variables

gbjbaanb
– gbjbaanb

2018-03-28 18:11:33 +00:00
Commented Mar 28, 2018 at 18:11 — gbjbaanb
– gbjbaanb, Commented Mar 28, 2018 at 18:11

marc_s · Accepted Answer · 2012-02-23 06:15:08Z

2

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!

answered Feb 23, 2012 at 6:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Argel Joseph Over a year ago

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

Argel Joseph Over a year ago

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.

colithium · Accepted Answer · 2012-02-23 06:03:56Z

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)

answered Feb 23, 2012 at 6:03

colithium

10.3k6 gold badges44 silver badges59 bronze badges

2 Comments

colithium Over a year ago

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

Shawn Kovac Over a year ago

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

Simon Wang · Accepted Answer · 2012-02-23 06:11:52Z

1

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

answered Feb 23, 2012 at 6:11

Simon Wang

3,0131 gold badge19 silver badges37 bronze badges

2 Comments

marc_s Over a year ago

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

Simon Wang Over a year ago

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

Collectives™ on Stack Overflow

Declaring SQL variables - SQL Server

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related