A stored procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a stored procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the stored procedure is executed to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the stored procedure my_proc:
DECLARE @result int
EXECUTE @result = my_proc
Return codes are commonly used in control-of-flow blocks within stored procedures to set the return code value for each possible error situation. You can use the @@ERROR function after a Transact-SQL statement to detect if an error occurred during the execution of the statement.
The following example shows the get_sales_for_title procedure with special handling that sets special return code values for various errors. The table shows the integer value assigned by the stored procedure to each possible error.
| Value | Meaning |
|---|---|
| 0 | Successful execution. |
| 1 | Required parameter value not specified. |
| 2 | Invalid parameter value specified. |
| 3 | Error occurred getting sales value. |
| 4 | NULL sales value found for the title. |
CREATE PROCEDURE get_sales_for_title
-- This is the input parameter, with a default.
@title varchar(80) = NULL,
-- This is the output parameter.
@ytd_sales int OUTPUT
AS
-- Validate the @title parameter.
IF @title IS NULL
BEGIN
PRINT "ERROR: You must specify a title value."
RETURN(1)
END
ELSE
BEGIN
-- Make sure the title is valid.
IF (SELECT COUNT(*) FROM titles
WHERE title = @title) = 0
RETURN(2)
END
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
-- Check for SQL Server errors.
IF @@ERROR <> 0
BEGIN
RETURN(3)
END
ELSE
BEGIN
-- Check to see if the ytd_sales value is NULL.
IF @ytd_sales IS NULL
RETURN(4)
ELSE
-- SUCCESS!!
RETURN(0)
END
GO
Using return codes in this manner allows your calling programs to detect and handle the errors that occur when the stored procedure is executed.
This example creates a program to handle the return codes returned from the get_sales_for_title procedure.
-- Declare the variables to receive the output value and return code -- of the procedure.
DECLARE @ytd_sales_for_title int, @ret_code INT
-- Execute the procedure with a title_id value
-- and save the output value and return code in variables.
EXECUTE @ret_code = get_sales_for_title
"Sushi, Anyone?",
@ytd_sales = @ytd_sales_for_title OUTPUT
-- Check the return codes.
IF @ret_code = 0
BEGIN
PRINT "Procedure executed successfully"
-- Display the value returned by the procedure.
PRINT 'Sales for "Sushi, Anyone?": ' + CONVERT(varchar(6),@ytd_sales_for_title)
END
ELSE IF @ret_code = 1
PRINT "ERROR: No title_id was specified."
ELSE IF @ret_code = 2
PRINT "ERROR: An invalid title_id was specified."
ELSE IF @ret_code = 3
PRINT "ERROR: An error occurred getting the ytd_sales."
GO