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

Question

在存储过程中，我在过程之上声明了两个表变量。现在，我试图在动态sql语句中使用该表变量，但是在执行该过程时遇到此错误。我正在使用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”。我试图将表放在动态查询的字符串块之外，但无济于事。

Answer 1

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

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

Answer 2

在SQL Server 2008+上，可以使用表值参数将表变量传递给动态SQL语句，只要您不需要更新表本身中的值即可。

因此，从您发布的代码中，您可以将这种方法用于@TSku但不能用于@RelPro

下面的示例语法。

CREATE TYPE MyTable AS TABLE 

( 

Foo int,

Bar int

);

GO

DECLARE @T AS MyTable;

INSERT INTO @T VALUES (1,2), (2,3)

SELECT *,

        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]

FROM @T

EXEC sp_executesql

  N'SELECT *,

        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]

    FROM @T',

  N'@T MyTable READONLY',

  @T=@T 

physloc包含该列只是为了证明子作用域中引用的表变量绝对与外部作用域相同，而不是副本。

Answer 3

你不具备使用动态SQL

update

    R

set

    Assoc_Item_1 = CASE WHEN @curr_row = 1 THEN foo.relsku ELSE Assoc_Item_1 END,

    Assoc_Item_2 = CASE WHEN @curr_row = 2 THEN foo.relsku ELSE Assoc_Item_2 END,

    Assoc_Item_3 = CASE WHEN @curr_row = 3 THEN foo.relsku ELSE Assoc_Item_3 END,

    Assoc_Item_4 = CASE WHEN @curr_row = 4 THEN foo.relsku ELSE Assoc_Item_4 END,

    Assoc_Item_5 = CASE WHEN @curr_row = 5 THEN foo.relsku ELSE Assoc_Item_5 END,

    ...

from

    (Select relsku From @TSku Where tid = @curr_row1) foo

    CROSS JOIN

    @RelPro R

Where

     R.RowID = @curr_row;