Returning Data by Using OUTPUT Parameters

Examples

The following example shows a stored procedure with an input and an output parameter. The first parameter in the stored procedure @SalesPerson receives the input value specified by the calling program, and the second parameter @SalesYTD is used to return the value to the calling program. The SELECT statement uses the @SalesPerson parameter to obtain the correct SalesYTD value, and assigns the value to the @SalesYTD output parameter.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
        DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
    GO
    CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
    @SalesPerson nvarchar(50),
    @SalesYTD money OUTPUT
    AS  
    
        SET NOCOUNT ON;
        SELECT @SalesYTD = SalesYTD
        FROM Sales.SalesPerson AS sp
        JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
        WHERE LastName = @SalesPerson;
    RETURN
    GO

The following statements execute the stored procedure with a value for the input parameter and saves the output value of the stored procedure in the @SalesYTD variable local to the calling program.

    -- Declare the variable to receive the output value of the procedure.
    DECLARE @SalesYTDBySalesPerson money;
    -- Execute the procedure specifying a last name for the input parameter
    -- and saving the output value in the variable @SalesYTDBySalesPerson
    EXECUTE Sales.uspGetEmployeeSalesYTD
        N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
    -- Display the value returned by the procedure.
    PRINT 'Year-to-date sales for this employee is ' + 
        convert(varchar(10),@SalesYTDBySalesPerson);
    GO

Input values can also be specified for OUTPUT parameters when the stored procedure is executed. This allows the stored procedure to receive a value from the calling program, change it or perform operations with it, then return the new value to the calling program. In the previous example, the @SalesYTDBySalesPerson variable can be assigned a value prior to executing the stored procedure. The @SalesYTD variable contains the value of the parameter in the body of the stored procedure, and the value of the @SalesYTD variable is returned to the calling program when the stored procedure exits. This is often referred to as "pass-by-reference capability."

If you specify OUTPUT for a parameter when you execute a stored procedure and the parameter is not defined using OUTPUT in the stored procedure, you get an error message. You can execute a stored procedure with OUTPUT parameters and not specify OUTPUT when executing the stored procedure. No error is returned, but you cannot use the output value in the calling program.

See Also

Reference

Concepts

Other Resources