表变量和执行 [英] Table variable and exec

问题描述

在执行命令字符串时如何使用表变量?

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);

返回以下错误

消息1087，第15级，州2，第1行 必须声明表变量"@FileIDs".

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

推荐答案

表@FileIDs不在exec(@testquery)的范围内.

这就是为什么您有这个问题.

That's why you have that problem.

要解决此问题，您可以使用temporary table:

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

或将表放入范围:

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

这篇关于表变量和执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！