usa usa - 4 months ago 22
SQL Question

SQL Server dynamic SQL with error of table variable not declared

I am trying to run a dynamic SQL query on SQL Server 2008:

DECLARE @cmd nvarchar(MAX)
DECLARE @tempTBL TABLE(value1 float)

SET @cmd = 'insert into ' + @tempTBL + ' select value from table11 as tb1 inner join table2 as tb2 on tb1.id = tb2.id where tb1.id2=''active'''

EXEC (@cmd )


I get an error:


Must declare the scalar variable "@tempTBL".


If I try

'insert into @tempTBL ...'


the same error happens.

I do not want to put
"declare @tempTBL"
in the
@cmd
because it will be run inside in a loop.

Why do I get this error?

Answer

You don't need to be using dynamic sql here at all.

declare @tempTBL table(value1 float)

insert into @tempTBL
select value 
from table11 as tb1  
inner join table2 as tb2 on tb1.id = tb2.id 
where tb1.id2 = 'active'