7

How can I use a table variable while executing a command string?

DECLARE @FileIDs TABLE 
(
   File_ID int
)
insert into @FileIDs select ID from Files where Name like '%bla%';

DECLARE @testquery as varchar(max);
set @testquery = 'select * from @FileIDs';
exec(@testquery);

returns the following error

Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@FileIDs".

alex555
  • 1,676
  • 4
  • 27
  • 45

3 Answers3

8

The table @FileIDs is not in the scope of exec(@testquery).

That's why you have that problem.

To solve this problem you may use a temporary table:

CREATE table #FileIDs
(
   File_ID int
)
insert into #FileIDs select ID from Files where Name like '%bla%'; 

DECLARE @testquery as varchar(max);
set @testquery = 'select * from #FileIDs';
exec(@testquery);

drop table #FileIDs

or put the table in the scope:

DECLARE @sql nvarchar(2000)

SET @sql='DECLARE @FileIDs TABLE (   File_ID int);'
SET @sql=@sql+'insert into @FileIDs select ID from Files where Name like ''%bla%'';'
set @sql=@sql+ 'select * from @FileIDs;'
EXECUTE sp_executesql @sql
aF.
  • 64,980
  • 43
  • 135
  • 198
  • For those who vote up: this is a copy&paste of [this original answer](https://stackoverflow.com/a/11360793/1426685) – d_f Aug 31 '21 at 10:59
4

Indeed table is out of the scope, try this:

DECLARE @sql nvarchar(2000)

SET @sql='DECLARE @FileIDs TABLE (   File_ID int);'
SET @sql=@sql+'insert into @FileIDs select ID from Files where Name like ''%bla%'';'
set @sql=@sql+ 'select * from @FileIDs;'
EXECUTE sp_executesql @sql
David Aleu
  • 3,922
  • 3
  • 27
  • 48
2
 CREATE table #FileIDs
(
   File_ID int
)
insert into #FileIDs select ID from Files where Name like '%bla%'; 

DECLARE @testquery as varchar(max);
set @testquery = 'select * from #FileIDs';
exec(@testquery);

drop table #FileIDs
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92