Passing table variable into dynamic SQL 2008

Question

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

Answer 1

Something like this should do it:

EXECUTE sp_executesql 
          N'SELECT * FROM @table',
          N'@table varchar(50)',
          @table= 'MyTableName';

Further information on sp_executeSQL can be found here

I would also recommend that you read this article from Erland Sommarskog

Answer 2

Here's an end-end example:

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