Returning Data by Using OUTPUT Parameters
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.
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.