Using OUTPUT Parameters in Stored Procedures

One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice
(
@AveragePrice money OUTPUT
)
AS
SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory)
SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

or

According to MS-BOL, SQL Server Stored-Procedures can return data in 4 forms:
1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

Specifying OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program. Lets check this with a simple example by using AdventureWorks database:

view source

print?

01	USE [AdventureWorks]

02

GO

03

04	--// Create Stored Prcedure with OUTPUT parameter

05	CREATE PROCEDURE getContactName

06	@ContactID INT,

07	@FirstName VARCHAR(50) OUTPUT,

08	@LastName  VARCHAR(50) OUTPUT

09

AS

10

BEGIN

11	SELECT @FirstName = FirstName, @LastName = LastName

12	FROM Person.Contact

13	WHERE ContactID = @ContactID

14

end

15

GO

16

17	--// Test the Procedure

18	DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50)

19

20	--/ Test# 1

21	SET @CID = 100

22	EXEC getContactName @ContactID=@CID,

23	@FirstName=@FName OUTPUT,

24	@LastName=@LName OUTPUT

25

26	SELECT @FName as 'First Name', @LName as 'Last Name'

27	--/ Output

28	-- ContactID    First Name  Last Name

29	-- 100          Jackie      Blackwell

30

31	--/ Test# 2

32	SET @CID = 200

33	EXEC getContactName @ContactID=@CID,

34	@FirstName=@FName OUTPUT,

35	@LastName=@LName OUTPUT

36

37	SELECT @FName as 'First Name', @LName as 'Last Name'

38	--/ Output

39	-- ContactID    First Name  Last Name

40	-- 200          Martin      Chisholm

41

GO

42

43	--// Final Cleanup

44	DROP PROCEDURE getContactName

45

GO