Return data with OUTPUT parameter from Stored Procedure in SQL Server 2005

Herewith, I am keeping my promise and moving ahead with topic of stored procedure. I will be explaining how to return data from stored procedure in SQL Server with OUTPUT parameter. If you are new to Stored Procedure than I kindly advice you to move to my prior article about stored procedure at:

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

When you have need to return data to the calling procedure, you should use of OUTPUT parameter of SQL Server in Stored Procedure.

If you are returning record set for single value, I strongly insist to use OUTPUT parameter as it is much much faster than returning the value.

--create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

--INSERT records

INSERT INTO emps

SELECT 'Ritesh','MIS','echem' UNION ALL

SELECT 'Bihag', 'MIS', 'CT' UNION ALL

SELECT 'Rajan', 'account','Marwadi' UNION ALL

SELECT 'Alka','account','tata' UNION ALL

SELECT 'Alpesh','Chemical','echem'

GO

--creating stored procedure which will return data with OUTPUT parameter

CREATE PROC getEmpDeptbyEmpName(@EmpName VARCHAR(50),@EmpDept VARCHAR(10) OUTPUT)

AS

SELECT @EmpDept=dept FROM emps WHERE Name=@EmpName

GO

--calling SP and catching return value in @EmpDept

DECLARE @EmpDept VARCHAR(50)

EXECUTE getEmpDeptbyEmpName 'Ritesh', @EmpDept OUTPUT

SELECT @EmpDept AS 'Department'

GO

Reference: Ritesh Shah
http://WWW.SQLHub.Com
Note: Microsoft Book online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com