Passing variable to sp_executesql

1. Create a Table (Emp) with required columns and data types

2. Declare a Variable (@EmpId) as INT

3. Define the String Variable (@EmpId INT) and pass it to sp_executesql as a first parameter definition.

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

EXAMPLE:

CREATE TABLE Emp

(

       EmpId INT IDENTITY(1,1) PRIMARY KEY,

       EmpName VARCHAR(100),

       EmpAge INT

)

GO -- REQUIRED for accessing the TYPE in next line

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 @EmpId AS INT = 2

DECLARE @SQL NVARCHAR(400)=''

SET @SQL = 'SELECT * FROM Emp WITH (NOLOCK) WHERE EmpId = @EmpId'

EXECUTE sp_executesql @SQL, N'@EmpId INT', @EmpId

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