If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the parameter’s current value to the calling program when the stored procedure exits. To save the parameter’s value in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.
The following example shows a stored procedure with an input and an output parameter. The first parameter in the stored procedure @title will receive the input value specified by the calling program, and the second parameter @ytd_sales will be 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 stored procedure’s output value 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 parameter’s value 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.
| EXECUTE | Scope of Transact-SQL Cursor Names |