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.