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
