Passing table variable to sp_executesql

1. Create a Type (Memory_Emp_Table_Type) with the format (columns and data types of each) of Table Variable. Note: This object can be kept permanently or it can be dropped once you done with it.   

2. Declare the Table Variable (@Emp)  as Created Type.

3. Define the Variable (@VarTblEmp) and pass it to sp_executesql as a first parameter definition with READONLY. 

4. Pass the Table Variable (@Emp) to the sp_executesql as second parameter. 

EXAMPLE:

IF EXISTS (SELECT * FROM SYS.TYPES WHERE NAME = 'Memory_Emp_Table_Type')

    DROP TYPE Memory_Emp_Table_Type

CREATE TYPE Memory_Emp_Table_Type AS TABLE

(

       EmpId INT IDENTITY(1,1) PRIMARY KEY,

       EmpName VARCHAR(100),

       EmpAge INT

)

GO -- REQUIRED for accessing the TYPE in next line

DECLARE @Emp AS Memory_Emp_Table_Type

INSERT INTO @Emp (EmpName, EmpAge)

       VALUES('Govindaraj Kanniappan',44)

INSERT INTO @Emp (EmpName, EmpAge)

       VALUES('Kanniappan Govindaraj',44)

INSERT INTO @Emp (EmpName, EmpAge)

       VALUES('Govindaraj K',44)

INSERT INTO @Emp (EmpName, EmpAge)

       VALUES('K Govindaraj',44)

DECLARE @SQL NVARCHAR(400)=''

SET @SQL = 'SELECT * FROM @VarTblEmp'

EXECUTE sp_executesql @SQL, N'@VarTblEmp Memory_Emp_Table_Type READONLY', @Emp

IF EXISTS (SELECT * FROM SYS.TYPES WHERE NAME = 'Memory_Emp_Table_Type')

    DROP TYPE Memory_Emp_Table_Type