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