如何在动态sql语句中使用表变量?

问题描述

在存储过程中，我在过程之上声明了两个表变量.现在，我试图在动态sql语句中使用该表变量，但是在执行该过程时遇到此错误.我正在使用Sql Server 2008.

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that procedure. I am using Sql Server 2008.

这就是我的查询的样子

set @col_name =  'Assoc_Item_'
              + Convert(nvarchar(2), @curr_row1);

set @sqlstat = 'update @RelPro set '
             + @col_name
             + ' = (Select relsku From @TSku Where tid = '
             + Convert(nvarchar(2), @curr_row1) + ') Where RowID = '
             + Convert(nvarchar(2), @curr_row);

Exec(@sqlstat);

我得到以下错误，

必须声明表变量"@RelPro".必须声明表变量"@TSku".

Must declare the table variable "@RelPro".Must declare the table variable "@TSku".

我试图将表移到动态查询的字符串块之外，但无济于事.

I have tried to take the table outside of the string block of dynamic query but to no avail.

推荐答案

您的EXEC在不同的上下文中执行，因此它不知道在原始上下文中已声明的任何变量.您应该能够使用临时表而不是表变量，如下面的简单演示所示.

Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.

create table #t (id int)

declare @value nchar(1)
set @value = N'1'

declare @sql nvarchar(max)
set @sql = N'insert into #t (id) values (' + @value + N')'

exec (@sql)

select * from #t

drop table #t

这篇关于如何在动态sql语句中使用表变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！