Leverage Table variables created outside of Dynamic SQL inside your Dynamic SQL

This is only for SQL 2008 and up.

Thanks firstly to Adrian Hills for this informative post that summarises very nicely how to do this.

The idea is rather simple, you want to create table variables outside of a Dynamic SQL statement but you want to access them from the DSQL on execution.

This is a big issue as the table variables on their own does not get accessed from EXECUTE sp_executesql as the variable is only valid for the context of the execution and the EXEC runs on a new context.

But you know this already, so how to make it happen.

/*1. Define a custom TABLE type, remember permission issues when creating a new type, make sure everyone that is executing this can execute this */
/*You can do this as you do this as you would a normal table, thus you can add more column or go crazy*/
CREATE TYPE MyCustomTableType AS TABLE (ID INTEGER)
GO
/*2. When you declare the table as the new custom type it inherits all the columns as defined*/
DECLARE @MyTable MyCustomTableType
INSERT @MyTable VALUES (1),(2),(3)
/*3. Now for the juicy bit, this is how you pass that table as a variable into the dynamic statement*/
EXECUTE sp_executesql N'SELECT * FROM @MyTable',
 N'@MyTable MyCustomTableType READONLY',
 @MyTable
GO

/*4. For multiple tables..*/
DECLARE @MyTable001 MyCustomTableType
DECLARE @MyTable002 MyCustomTableType

INSERT @MyTable001 VALUES (1),(2),(3)
INSERT @MyTable002 VALUES (4),(5),(6)

EXECUTE sp_executesql
N'SELECT * FROM @MyTable001 UNION ALL SELECT * FROM @MyTable002' /*The body to execute*/
, N'@MyTable001 MyCustomTableType READONLY,@MyTable002 MyCustomTableType READONLY' /*Declare your variables:@1,@2,@3... etc.*/
, @MyTable001,@MyTable002 /*Populate your variables:@1=a,@2=b..etc. in order they were declared above*/
GO

DROP TYPE MyCustomTableType
GO

But what is happening?

Thanks to SQL Server 2008 you can now pass table valued parameters..

So all we are doing is declaring a table and populating it, then declaring a similar table as a variable and passing through the values from one to the other.

Simple aint it… now I just need to go rewrite about 17 bazillion stored procedures, but it is going to be worth it.

Adrian