How to Pass Table Variables to Dynamic SQL Statements in SQL Server?

Passing table variables to dynamic SQL statements in SQL Server

When building stored procedures, you may encounter the challenge of referencing declared table variables in dynamic SQL statements. This may result in an error stating that the variable needs to be declared.

Consider the following example:

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

Executing this statement may produce the following error:

• Table variable "@RelPro" must be declared.
• Table variable "@TSku" must be declared.

To solve this problem, table-valued parameters (TVPs) can be used. TVP allows table variables to be passed to dynamic SQL statements without explicitly declaring them. However, please note: this method cannot be used to update values ​​in the table.

For example, consider the following modified code:

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 

This code demonstrates that the table variable referenced in the subscope (within the dynamic statement) is the same as the table variable in the outer scope, thus avoiding the error. The key is to use sp_executesql and declare TVP types and parameters correctly. For update operations, a different approach is required, such as using cursors or other alternatives.

The above is the detailed content of How to Pass Table Variables to Dynamic SQL Statements in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!