Passing a table variable to dynamic SQL 2008

Question

I need to run a dynamic sql that uses a table variable created in the parent scope. How to pass table variable to dynamic sql in SQL2008?

Answer 1

Here is an example of the end:

-- Define a custom TABLE type
CREATE TYPE IntegerTableType AS TABLE (ID INTEGER);

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