T-SQL RETURN Statement

Master T-SQL RETURN — exit a batch, procedure, or function immediately. Integer status codes from procedures (with EXEC @rc = ...), early-exit guard clauses for input validation, and the RETURN-in-functions distinction.

RETURN immediately stops execution of the current batch, stored procedure, or function. From a procedure, you can optionally return a small integer status code to whoever called it — by convention, 0 means success and any non-zero value means some kind of failure or special outcome.

Where You'll Use It

• Early exit on bad input. Validate parameters at the top of a procedure; RETURN immediately if anything's wrong, leaving the main logic at the un-indented top level.
• Status codes. Communicate success/failure to the caller in addition to (or instead of) result sets and OUTPUT parameters.
• Function results. In a scalar user-defined function, RETURN expression is how you produce the function's value.

• integer_expression — optional. If you omit it from a procedure, RETURN exits with status code 0.
• The expression must be an integer (or implicitly convertible to one). Strings, decimals, dates etc. are not allowed in a procedure's RETURN — those go through OUTPUT parameters or result sets.

CREATE PROCEDURE CheckValue
    @Value INT
AS
BEGIN
    IF @Value < 0
    BEGIN
        PRINT 'The value is negative';
        RETURN 1;                -- error status
    END
    ELSE
    BEGIN
        PRINT 'The value is positive or zero';
        RETURN 0;                -- success
    END
END
GO

Now call it with a negative value and capture the return code:

DECLARE @returnValue INT;

EXEC @returnValue = CheckValue -5;

PRINT 'Return Value: ' + CAST(@returnValue AS NVARCHAR(10));

And with a positive value:

DECLARE @returnValue INT;

EXEC @returnValue = CheckValue 10;

PRINT 'Return Value: ' + CAST(@returnValue AS NVARCHAR(10));

The "guard clause" pattern — fail fast, then run the real logic flat:

CREATE OR ALTER PROCEDURE ProcessOrder
    @OrderID    INT,
    @CustomerID INT
AS
BEGIN
    -- Guard clauses — bail out on any bad input
    IF @OrderID IS NULL
    BEGIN
        PRINT 'Error: OrderID is required';
        RETURN 1;
    END

    IF @CustomerID IS NULL
    BEGIN
        PRINT 'Error: CustomerID is required';
        RETURN 2;
    END

    IF @OrderID <= 0 OR @CustomerID <= 0
    BEGIN
        PRINT 'Error: IDs must be positive';
        RETURN 3;
    END

    -- Main work — at the top level, no nesting
    PRINT 'Processing order ' + CAST(@OrderID    AS VARCHAR(10)) +
          ' for customer '   + CAST(@CustomerID AS VARCHAR(10));
    RETURN 0;
END
GO

-- Try with a bad input
DECLARE @rc INT;
EXEC @rc = ProcessOrder @OrderID = NULL, @CustomerID = 42;
PRINT 'Status: ' + CAST(@rc AS VARCHAR(10));

In a function, RETURN's argument is the function's value — and it can be any declared type, not just an integer:

CREATE OR ALTER FUNCTION dbo.CircleArea (@radius DECIMAL(10, 4))
RETURNS DECIMAL(20, 6)
AS
BEGIN
    DECLARE @pi DECIMAL(20, 6) = 3.141593;
    RETURN @pi * @radius * @radius;       -- returns a DECIMAL, not an INT
END
GO

SELECT dbo.CircleArea(5.0) AS Area;

• RETURN immediately exits the current batch, stored procedure, or function.
• From a stored procedure, the optional argument is an integer status code — by convention 0 = success, non-zero = error/special outcome.
• To send back text, decimals, dates, or multiple values from a procedure, use OUTPUT parameters or a result set — not RETURN.
• In a function, RETURN's argument is the function's value, and can be any declared data type.
• Use early RETURN as a guard-clause pattern: validate inputs at the top, fail fast, leave the main logic flat.