Returning Data 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 @title receives the input value specified by the calling program, and the second parameter @ytd_sales is used to return the value to the calling program. The SELECT statement uses the @title parameter to obtain the correct ytd_sales value, and assigns the value to the @ytd_sales output parameter.
CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
RETURN
GO
The following program executes the stored procedure with a value for the input parameter and saves the output value of the stored procedure in the @ytd_sales_for_title variable local to the calling program.
-- Declare the variable to receive the output value of the procedure.
DECLARE @ytd_sales_for_title int
-- Execute the procedure with a title_id value
-- and save the output value in a variable.
EXECUTE get_sales_for_title
"Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT
-- Display the value returned by the procedure.
PRINT 'Sales for "Sushi, Anyone?": ' + convert(varchar(6),@ytd_sales_for_title)
GO
Sales for "Sushi, Anyone?": 4095
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 earlier example, the @ytd_sales_for_title variable can be assigned a value prior to executing the stored procedure. The @ytd_sales variable contains the value of the parameter in the body of the stored procedure, and the value of the @ytd_sales 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.