MSDN Documentation

Overview

A stored procedure can return an integer status value using the RETURN statement. This value is often used to indicate success, failure, or a specific condition calculated by the procedure.

Unlike output parameters, the return value is limited to int and is accessed after the procedure execution completes.

Syntax

CREATE PROCEDURE [ schema_name. ] procedure_name
    [ { @parameter_name [AS] [type_schema_name.] data_type
        [ = default ] [OUT | OUTPUT] } [ ,...n ] ]
AS
BEGIN
    -- procedure_body
    RETURN integer_expression
END

Only one RETURN statement is permitted per execution path. If no RETURN is specified, the procedure returns 0 by default.

Example

The following procedure validates a numeric input and returns different status codes:

CREATE PROCEDURE dbo.ValidateNumber
    @Value INT
AS
BEGIN
    IF @Value IS NULL
        RETURN -1;   -- Null input
    IF @Value < 0
        RETURN -2;   -- Negative value
    IF @Value > 100
        RETURN -3;   -- Value too large
    RETURN 0;        -- Success
END;

Calling the procedure and capturing its return value:

DECLARE @Result INT;
EXEC @Result = dbo.ValidateNumber @Value = 42;
SELECT @Result AS ReturnCode;

Best Practices

• Use return values for simple status codes or error numbers.
• Prefer output parameters for returning multiple or non‑integer values.
• Document each possible return code in the procedure’s comments.
• Reserve 0 for success and use negative numbers for errors.

Try It