When a CREATE PROCEDURE statement and an EXECUTE statement both include the OUTPUT option with a parameter name, the procedure can use a variable to return the value of that parameter to the caller. By using the OUTPUT keyword, any changes to the parameter that result from the execution of the procedure are retained even after the procedure finishes executing, and the variable can subsequently be used in additional SQL statements in the batch or calling procedure. This is often referred to as a "pass-by-reference capability." If the OUTPUT keyword is not used, changes to the parameter are not retained after the procedure finishes executing. In addition, a value can be returned directly ("pass by value") using the RETURN statement.
A single stored procedure can use any or all of these capabilities to return:
The values passed in the parameters must be passed in this form:
@parameter = @variable [OUTput]
If you specify output while you're executing a procedure and the parameter is not defined using output in the stored procedure, you'll get an error message. It is not wrong to call a procedure that includes return value specifications without requesting the return values with output ¾ you just won't get the return values. The stored procedure writer controls what information users can access, and users have control over their variables.
A stored procedure can also return values via "output" parameters. Each must be defined as an OUTPUT variable in the stored procedure and in the calling statements, as in the following example. These values can then subsequently be selected.
myproc @a = @myvara out, @b = @myvarb out
You cannot pass constants. There must be a variable name to receive the return value. The parameters can be of any SQL Server datatype except text or image. If the stored procedure requires several parameters, pass the return value parameter last in the EXECUTE statement or pass all subsequent parameters in this form:
@parameter = [value | @variable [OUTput]]
The following procedure performs division of two integer variables. The third integer variable, @quotient, is defined as an output parameter:
CREATE PROC divide
@dividend smallint,
@divisor smallint,
@quotient int output
AS
IF @divisor = 0
BEGIN
SELECT @quotient = NULL
RETURN -100
END
SELECT @quotient = @dividend / @divisor
RETURN 0
If you execute this procedure by providing two integer constants and an OUTPUT parameter, the procedure first checks for an attempt to divide by 0. If the divisor is not 0, the procedure performs the division, puts the answer in the OUTPUT parameter, and sets the return value to 0. For example, if you execute this procedure using 64 and 8 as the integer constants and @quot_parm as the OUTPUT parameter, the result of the division, 8, is put in the @quot_parm parameter:
DECLARE @quot_parm int
DECLARE @retstat int
EXEC @retstat = divide 64, 8, @quot_parm OUTPUT
SELECT @retstat, @quot_parm
----------- -----------
0 8
(1 row(s) affected)
By using the OUTPUT keyword, any changes to @quot_parm that result from the execution of the divide procedure are retained even after the procedure finishes executing. If the OUTPUT keyword is not used, changes to @quot_parm are not retained.
If you execute this procedure with 0 as the divisor, the procedure sets @quotient to NULL and the return value to - 100, as shown in the following example:
DECLARE @quot_parm int DECLARE @retstat int EXEC @retstat = divide 64, 0, @quot_parm OUTPUT SELECT @retstat, @quot_parm ----------- ----------- -100 NULL (1 row(s) affected)