Using RETURN
The RETURN statement unconditionally terminates a query, stored procedure, or batch. None of the statements in a stored procedure or batch following the RETURN statement are executed.
When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0.
Most stored procedures follow the convention of using the return code to indicate the success or failure of the stored procedure. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred. For example:
USE Northwind
GO
-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
@MaxQuantity INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
-- Do a SELECT using the input parameter.
SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm
-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
GO
A Transact-SQL batch or stored procedure that executes a stored procedure can retrieve the return code into an integer variable:
DECLARE @ReturnStatus INT
DECLARE @MaxQtyVariable INT
EXECUTE @ReturnStatus = SampleProcedure @EmployeeIDParm = 9,
@MaxQtyVariable = @MaxQuantity OUTPUT
-- Show the values returned.
PRINT ' '
PRINT 'Return code = ' + CAST(@ReturnStatus AS CHAR(10))
PRINT 'Maximum Quantity = ' + CAST(@MaxQtyVariable AS CHAR(10))
GO
Applications that call a stored procedure can bind to an integer variable a parameter marker corresponding to the return code.