SQL Server Documentation

Stored Procedure Return Values

Stored procedures in SQL Server can return a status code to indicate the success or failure of the procedure's execution. This is achieved using the RETURN statement.

Understanding the RETURN Statement

The RETURN statement is used to exit a stored procedure and pass a status value back to the caller. By convention:

• A return value of 0 typically indicates success.
• A non-zero return value typically indicates an error or a specific condition.

Syntax

CREATE PROCEDURE procedure_name
    -- parameters
AS
BEGIN
    -- procedure logic
    IF some_condition_is_met
    BEGIN
        RETURN 0 -- Indicate success
    END
    ELSE
    BEGIN
        RETURN 1 -- Indicate failure or a specific error
    END
END
GO

Example: Basic Return Value

Consider a stored procedure that attempts to delete a record. It returns 0 if the delete is successful and 1 if the record was not found.

CREATE PROCEDURE usp_DeleteCustomer
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RowsAffected INT;

    DELETE FROM Customers
    WHERE CustomerID = @CustomerID;

    SET @RowsAffected = @@ROWCOUNT;

    IF @RowsAffected = 1
    BEGIN
        PRINT 'Customer deleted successfully.';
        RETURN 0; -- Success
    END
    ELSE
    BEGIN
        PRINT 'Customer not found or no rows deleted.';
        RETURN 1; -- Failure/Not Found
    END
END
GO

Executing a Procedure and Checking the Return Value

You can execute a stored procedure and capture its return value using the EXEC or EXECUTE statement with the RETURN keyword.

DECLARE @ReturnStatus INT;

-- Execute the procedure
EXEC @ReturnStatus = usp_DeleteCustomer @CustomerID = 101;

-- Check the return status
IF @ReturnStatus = 0
BEGIN
    PRINT 'Operation completed successfully.';
END
ELSE IF @ReturnStatus = 1
BEGIN
    PRINT 'Operation failed: Customer not found.';
END
ELSE
BEGIN
    PRINT 'Operation completed with an unknown status code: ' + CAST(@ReturnStatus AS VARCHAR);
END
GO

Using Return Values for Control Flow

Return values are crucial for building robust applications. They allow calling programs (like application code or other stored procedures) to make decisions based on the outcome of a stored procedure.

Considerations

• Error Handling: Use return values in conjunction with the RAISERROR or THROW statements for more comprehensive error reporting.
• Output Parameters: For returning multiple values or complex data, consider using OUTPUT parameters instead of (or in addition to) return values.
• Default Value: If a stored procedure completes without an explicit RETURN statement, it implicitly returns 0.
• Data Type: The return value is an integer.