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) http://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query or http://stackoverflow.com/questions/1325044/dynamic-sql-passing-table-name-as-parameter or http://stackoverflow.com/questions/10521144/how-do-i-use-sql-server-table-name-in-select-query-with-a-variable... take your pick all say dynamic SQL — xQbert, Jul 08 '14 at 19:14
I don't get it. How is it changing? It's not being used in the loop. More code? :-p — Relevant, Jul 08 '14 at 19:19
I'd recommend avoiding dynamic sql if you can find another way: http://www.sommarskog.se/dynamic_sql.html — Relevant, Jul 08 '14 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, Jul 08 '14 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, Jul 08 '14 at 19:28

score 0 · Accepted Answer · answered Jul 08 '14 at 19:27

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

How to use a variable in SQL statement From Line

1 Answers1