Returning Data Using a Return Code

Returning Data Using a Return Code

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.

Examples

A. Return a different return code depending on the type of error

This 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.

B. Handle the different return codes returned from a stored procedure

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