Returning Data by 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, such as:
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 whether an error occurred during the execution of the statement.
Examples
A. Returning a different return code based on the type of error
The following example shows the usp_GetSalesYTD procedure with error handling that sets special return code values for various errors. The following table shows the integer value that is assigned by the stored procedure to each possible error, and the corresponding meaning for each value.
Return code value |
Meaning |
|---|---|
0 |
Successful execution. |
1 |
Required parameter value is not specified. |
2 |
Specified parameter value is not valid. |
3 |
Error has occurred getting sales value. |
4 |
NULL sales value found for the salesperson. |
USE AdventureWorks;
GO
IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.usp_GetSalesYTD;
GO
CREATE PROCEDURE Sales.usp_GetSalesYTD
@SalesPerson nvarchar(50) = NULL, -- NULL default value
@SalesYTD money = NULL OUTPUT
AS
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify a last name for the sales person.'
RETURN(1)
END
ELSE
BEGIN
-- Make sure the value is valid.
IF (SELECT COUNT(*) FROM HumanResources.vEmployee
WHERE LastName = @SalesPerson) = 0
RETURN(2)
END
-- Get the sales for the specified name and
-- assign it to the output parameter.
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID
WHERE LastName = @SalesPerson;
-- 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 @SalesYTD IS NULL
RETURN(4)
ELSE
-- SUCCESS!!
RETURN(0)
END
-- Run the stored procedure without specifying an input value.
EXEC Sales.usp_GetSalesYTD;
GO
-- Run the stored procedure with an input value.
DECLARE @SalesYTDForSalesPerson money, @ret_code int;
-- Execute the procedure specifying a last name for the input parameter
-- and saving the output value in the variable @SalesYTD
EXECUTE Sales.usp_GetSalesYTD
N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
PRINT N'Year-to-date sales for this employee is ' +
CONVERT(varchar(10), @SalesYTDForSalesPerson);
B. Handling the different return codes that are returned from a stored procedure
The following example creates a program to handle the return codes that are returned from the usp_GetSalesYTD procedure.
-- Declare the variables to receive the output value and return code
-- of the procedure.
DECLARE @SalesYTDForSalesPerson money, @ret_code int;
-- Execute the procedure with a title_id value
-- and save the output value and return code in variables.
EXECUTE @ret_code = Sales.usp_GetSalesYTD
N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
-- Check the return codes.
IF @ret_code = 0
BEGIN
PRINT 'Procedure executed successfully'
-- Display the value returned by the procedure.
PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson)
END
ELSE IF @ret_code = 1
PRINT 'ERROR: You must specify a last name for the sales person.'
ELSE IF @ret_code = 2
PRINT 'EERROR: You must enter a valid last name for the sales person.'
ELSE IF @ret_code = 3
PRINT 'ERROR: An error occurred getting sales value.'
ELSE IF @ret_code = 4
PRINT 'ERROR: No sales recorded for this employee.'
GO
See Also