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.