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