Specifying the Direction of a Parameter
The direction of a parameter is either in, meaning a value is passed to the stored procedure input parameter, or out, meaning the stored procedure returns a value to the calling program via an output parameter. The default is an input parameter.
To specify an output parameter, you must specify the OUTPUT keyword in the definition of the parameter in the stored procedure. The stored procedure returns the current value of the output parameter to the calling program when the stored procedure exits. The calling program must also use the OUTPUT keyword when executing the stored procedure to save the parameter's value in a variable that can be used in the calling program. For more information, see Returning Data by Using OUTPUT Parameters.
Examples
The following example creates the Production.usp_GetList stored procedure, which returns a list of products that have prices that do not exceed a specified amount. The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statement to access a value set during the procedure execution.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
Execute usp_GetList to return a list of Adventure Works products (Bikes) that cost less than $700. The OUTPUT parameters @cost and @compareprices are used with control-of-flow language to return a message in the Messages window.
Note
The OUTPUT variable must be defined during the procedure creation as well as during the use of the variable. The parameter name and variable name do not have to match. However, the data type and parameter positioning must match (unless @listprice= variable is used).
DECLARE @ComparePrice money, @Cost money
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
Here is the partial result set:
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.
See Also
Concepts
Specifying a Parameter Name
Specifying a Parameter Data Type
Specifying a Parameter Default Value
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|