Passing a TABLE variable into dynamic SQL

A question popped up on StackOverflow today, asking how to pass a TABLE variable into a dynamic SQL statement in SQL Server 2008. I’ve previously blogged about table-valued parameters, comparing the approach of passing in a TABLE of values to a stored procedure to the techniques you’d have had to use in earlier versions of SQL Server (e.g. CSV or XML), but this specific question is worth a quick follow-up.

Passing a TABLE variable in to a dynamic SQL statement is straight forwards:

-- First define a custom TABLE type  
CREATE TYPE IntegerTableType AS TABLE (ID INTEGER)  
GO  
  
-- Fill a var of that type with some test data  
DECLARE @MyTable IntegerTableType  
INSERT @MyTable VALUES (1),(2),(3)  
  
-- Now this is how you pass that var into dynamic statement  
EXECUTE sp_executesql N'SELECT * FROM @MyTable',   
    N'@MyTable IntegerTableType READONLY',   
    @MyTable  

Job done.


See also