• Article

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 AdventureWorks2008R2;
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
    SET NOCOUNT ON;
    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.