Friday 23 March 2012

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?
01USE [AdventureWorks]
02GO
03 
04--// Create Stored Prcedure with OUTPUT parameter
05CREATE PROCEDURE getContactName
06    @ContactID INT,
07    @FirstName VARCHAR(50) OUTPUT,
08    @LastName  VARCHAR(50) OUTPUT
09AS
10BEGIN
11    SELECT @FirstName = FirstName, @LastName = LastName
12    FROM Person.Contact
13    WHERE ContactID = @ContactID
14end
15GO
16 
17--// Test the Procedure
18DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50)
19 
20--/ Test# 1
21SET @CID = 100
22EXEC getContactName @ContactID=@CID,
23                    @FirstName=@FName OUTPUT,
24                    @LastName=@LName OUTPUT
25 
26SELECT @FName as 'First Name', @LName as 'Last Name'
27--/ Output
28-- ContactID    First Name  Last Name
29-- 100          Jackie      Blackwell
30 
31--/ Test# 2
32SET @CID = 200
33EXEC getContactName @ContactID=@CID,
34                    @FirstName=@FName OUTPUT,
35                    @LastName=@LName OUTPUT
36 
37SELECT @FName as 'First Name', @LName as 'Last Name'
38--/ Output
39-- ContactID    First Name  Last Name
40-- 200          Martin      Chisholm
41GO
42 
43--// Final Cleanup
44DROP PROCEDURE getContactName
45GO

2 comments: