using output parameters in sql server procedures | output parameter example in sql server

Hi in this post i will show how to use output parameter in sql stored procedures:

Example :

select * from employee

Now using this above we will create a stored procedure where we will make use of output parameter.

CREATE PROCEDURE getEmpDetails @empDeptId INT
 ,@Name VARCHAR(50) OUTPUT
 ,@salary NUMERIC(18) OUTPUT
AS
BEGIN
 SELECT @Name = EmpName
  ,@salary = Salary
 FROM employee
 WHERE EmpDeptID = @empDeptId
END
GO

Testing :

DECLARE @EID INT
 ,@EName VARCHAR(50)
 ,@ESal NUMERIC(18)

SET @EID = 3

EXEC getEmpDetails @empDeptId = @EID
 ,@Name = @EName OUTPUT
 ,@salary = @ESal OUTPUT

SELECT @EName AS 'First Name'
 ,@ESal AS 'Last Name'

PRINT @Ename
PRINT @ESal