How to use a variable in SQL statement From Line

Question

I am trying to create a procedure that allows for a variable to be used as a table name.

Example:

Select Temp1, Temp2 
From @var

The @var has the correct table name but changes with each iteration through the while loop.

-- Above sets up all Variables
WHILE @Count >= @I
BEGIN 
SET @ObjectID = (SELECT ObjectID FROM @TmpTable WHERE ID = @I)
SET @TableName = (SELECT TableName FROM @TmpTable WHERE ID = @I)
IF @I = 8
BEGIN

        SELECT * 
        INTO ##TMPTable
        FROM @TableName
        -- Additional Processing
END
END

you can't without using dynamic SQL (Building a string with the select and then executing that string) stackoverflow.com/questions/20678725/… or stackoverflow.com/questions/1325044/… or stackoverflow.com/questions/10521144/…... take your pick all say dynamic SQL — xQbert
– xQbert, Commented Jul 8, 2014 at 19:14
I don't get it. How is it changing? It's not being used in the loop. More code? :-p — Relevant
– Relevant, Commented Jul 8, 2014 at 19:19
I'd recommend avoiding dynamic sql if you can find another way: sommarskog.se/dynamic_sql.html — Relevant
– Relevant, Commented Jul 8, 2014 at 19:20
Do you even need a loop here? What is the "additional processing"? This sort of screams of poor design but there isn't much information to go on. Of course they way you have this coded the loop seems really pointless since you are only going to anything when @I = 8. — Sean Lange
– Sean Lange, Commented Jul 8, 2014 at 19:23
@I= 8 is only for testing so that I can work with one table. The main goal is to copy data to a temp table so that we can recreate a schema. — epelletier9740
– epelletier9740, Commented Jul 8, 2014 at 19:28

datamodel · Accepted Answer · 2014-07-08 19:27:52Z

0

DECLARE   
   @SQLString varchar(max),  
   @TempTableName varchar(100)  

SET @TempTableName = '##TMPTable' + cast( @@spid AS varchar(5))  

WHILE @Count >= @I
BEGIN 
SET @ObjectID = (SELECT ObjectID FROM @TmpTable WHERE ID = @I)
SET @TableName = (SELECT TableName FROM @TmpTable WHERE ID = @I)
IF @I = 8
BEGIN

        set @SQLString = '
        SELECT * 
        INTO ' + @TempTableName + '
        FROM ' + @TableName

        EXECUTE ( @SQLString )  

        EXECUTE ( 'SELECT * from ' + @TempTableName)  

        -- Additional Processing
END
END

answered Jul 8, 2014 at 19:27

datamodel

1167 bronze badges

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

Collectives™ on Stack Overflow

How to use a variable in SQL statement From Line

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related