Stored Procedures - Output Parameters & Return Values

Besides using input parameters, stored procedures can also return OUTPUT parameters and return values. Output parameters behave similarly to input parameters, but have to be declared with the OUTPUT keyword. In addition, you should specify the OUTPUT keyword when executing a stored procedure containing the output parameter to get the value. The following procedure contains an input parameter of the title type and an output parameter of total quantity of titles sold for the specified type:
1.
CREATE PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT
2.
AS
3.
SELECT SUM(qty) FROM sales a, titles b
4.
WHERE
5.
a.title_id = b.title_id
6.
and
7.
b.type = @type


This procedure can be executed as follows:
1.
DECLARE @total_sales_business int
2.
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT


Results:
1.
-----------
2.
90


Notice that in order to use the output parameter, we have to declare a variable with the same data type as the output parameter of the called stored procedure. We can easily extend the same procedure to return more than one output parameters:
1.
ALTER PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT, @avg_sales INT OUTPUT
2.
AS
3.
SELECT SUM(qty), AVG(qty) FROM sales a, titles b
4.
WHERE
5.
a.title_id = b.title_id
6.
and
7.
b.type = @type


Now, we can execute the new procedure as follows:
1.
DECLARE @total_sales_business INT, @avg_sales_business INT
2.
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT,
3.
@avg_sales = @avg_sales_business OUTPUT


Results:
1.
----------- -----------
2.
90 18


You can execute the stored procedure with an output parameter without the OUTPUT keyword, but you won't be able to use the returned value in the calling program.
Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure (or any group of SQL statements) will return 0. The syntax of the return command is:
1.
RETURN integer_value


You can check the result of executing a stored procedures with return values as follows:
1.
EXEC @return_variable = stored_procedure_name


where @return_variable is a numeric variable used to check the return value.
You can optionally enclose the integer value in parenthesis. If you don't supply the integer value, SQL Server will provide a value for you, depending on the state of program execution. RETURN also unconditionally exits the program, so once a RETURN is encountered in your T-SQL code SQL Server will not check any other conditions.
The following example demonstrates usage of user-defined return codes; notice that even though multiple conditions are examined within the procedure, a single RETURN will cause the program to stop and return the appropriate value:


01.
ALTER PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT, @avg_sales INT OUTPUT
02.
AS
03.
IF @type IS NULL
04.
BEGIN PRINT 'type is required' RETURN (1)
05.
END
06.
SELECT @total_sales=SUM(qty), @avg_sales = AVG(qty) FROM sales a, titles b
07.
WHERE
08.
a.title_id = b.title_id
09.
and
10.
b.type = @type
11.
IF @total_sales IS NULL
12.
AND @avg_sales IS NULL
13.
BEGIN
14.
RETURN (3) -- both avg and sum are null
15.
END
16.
IF @avg_sales IS NULL
17.
BEGIN
18.
RETURN (1) -- avg is null
19.
END
20.
IF @total_sales IS NULL
21.
BEGIN
22.
RETURN (2) -- total is null
23.
END


Now, we can execute the procedure with intentionally wrong values to check how the RETURN statement works:


1.
DECLARE @total_sales_business INT, @avg_sales_business INT, @return_status INT
2.
EXEC @return_status = sales_for_type tomato, @total_sales=@total_sales_business OUTPUT,
3.
@avg_sales = @avg_sales_business OUTPUT
4.
SELECT @return_status


Result:


1.
-----------
2.
3
3.
DECLARE @total_sales_business INT, @avg_sales_business INT, @return_status INT
4.
EXEC @return_status = sales_for_type NULL, @total_sales=@total_sales_business OUTPUT,
5.
@avg_sales = @avg_sales_business OUTPUT
6.
SELECT @return_status


Result:


1.
type is required
2.
-----------
3.
1