1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
usa
  • 43
  • 3
  • 1
    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 Aug 04 '16 at 20:07
  • 4
    You can see [this](http://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement) and [this](http://stackoverflow.com/questions/30891932/how-to-insert-into-a-table-variable-with-a-dynamic-query) for solutions – techspider Aug 04 '16 at 20:09
  • 5
    "run inside in a loop" <-- red flag! Are you sure you need a loop? What are you trying to do? – Blorgbeard Aug 04 '16 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 Aug 04 '16 at 20:23

1 Answers1

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'
Sean Lange
  • 33,028
  • 3
  • 25
  • 40