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 AdventureWorks;
GO
-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
@MaxTotal 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 c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE EmployeeID = @EmployeeIDParm;
-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR;
-- Set a value in the output parameter.
SELECT @MaxTotal = MAX(TotalDue)
FROM Sales.SalesOrderHeader;
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 @MaxTotalVariable int;
DECLARE @MaxTotal int;
EXECUTE @ReturnStatus = SampleProcedure @EmployeeIDParm = 65 ,@MaxTotal = @MaxTotalVariable OUTPUT;
PRINT ' ';
PRINT 'Return code = ' + CAST(@ReturnStatus AS CHAR(10));
PRINT 'Maximum Order Total = ' + CAST(@MaxTotalVariable AS CHAR(15));
GO
Applications that call a stored procedure can bind to an integer variable a parameter marker corresponding to the return code.
See Also
Concepts
Parameter Markers (Database Engine)
Other Resources
EXECUTE (Transact-SQL)
RETURN (Transact-SQL)