How to use table variable in a dynamic sql statement?

Question

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.

This is how my query looks like,

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

And I get the following errors,

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.

Answer 1

On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don't need to update the values in the table itself.

So from the code you posted you could use this approach for @TSku but not for @RelPro

Example syntax below.

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  

The physloc column is included just to demonstrate that the table variable referenced in the child scope is definitely the same one as the outer scope rather than a copy.