Passing and Retrieving variable value from sp_executesql

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

2.       Declare a Input Variable (@InEmpId) as INT

3.       Declare the required Output Variables (@OutEmpName) as VARCHAR(100) = '' and @OutEmpAge INT = 0)

4.       Define the Input String Variable (@InEmpId INT) Output and String Variables @OutEmpName VARCHAR(100) OUTPUT, @OutEmpAge INT OUTPUT pass it to sp_executesql as a first parameter definition

5.       Pass the input Variable (@InEmpId) and Output variables @OutEmpName = @OutEmpName OUTPUT, @OutEmpAge = @OutEmpAge OUTPUT 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',41)

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('Kanniappan Govindaraj',42)

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('Govindaraj K',43)

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('K Govindaraj',44)

GO

DECLARE @InEmpId AS INT = 2

DECLARE @OutEmpName AS VARCHAR(100) = '', @OutEmpAge INT = 0

DECLARE @SQL NVARCHAR(400)=''

SET @SQL = 'SELECT @OutEmpName = EmpName, @OutEmpAge = EmpAge FROM Emp WITH (NOLOCK) 

WHERE EmpId = @InEmpId'

EXECUTE sp_executesql @SQL, N'@InEmpId INT, @OutEmpName VARCHAR(100) OUTPUT, @OutEmpAge INT OUTPUT', @InEmpId, @OutEmpName = @OutEmpName OUTPUT, @OutEmpAge = @OutEmpAge OUTPUT

SELECT @OutEmpName AS EmpName, @OutEmpAge AS Age