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 t