SQL Server dynamic SQL with error of table variable not declared

Question

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?

you can't use table variables in dynamic queries; change that to temp table, unless complete script including declaration/usage of your table variable resides inside your dynamic query — techspider
– techspider, Commented Aug 4, 2016 at 20:07
"run inside in a loop" <-- red flag! Are you sure you need a loop? What are you trying to do? — Blorgbeard
– Blorgbeard, Commented Aug 4, 2016 at 20:10
@usa - step back for a sec and consider where you are going with this. What are you trying to accomplish here? Anything a single column table var can do, a subquery can handle...and the dynamic SQL here only appears to be adding complexity, why are you going this route? As per above comment, saying this will be used in a loop raises so many red flags that you are likely doing something in a very inefficient manner. — Twelfth
– Twelfth, Commented Aug 4, 2016 at 20:23

Sean Lange · Accepted Answer · 2016-08-04 20:11:09Z

1

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'

answered Aug 4, 2016 at 20:11

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

SQL Server dynamic SQL with error of table variable not declared

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