RETURN Statement (Transact-SQL)

Applies to: SQL Server (all supported versions)

Specifies that control should be passed from the current Transact-SQL statement batch or stored procedure. If RETURN is used in a stored procedure, it can optionally return a value to the caller of the procedure.

Syntax

RETURN [ integer_expression ] 

Arguments

integer_expression

Is an integer value returned to the caller. This expression can be a literal, a variable, or the result of a calculation. If no expression is specified, a null value is returned.

The value can be a positive or negative integer.

Return Value

The RETURN statement can be used to exit a batch, stored procedure, or trigger. When used in a stored procedure, it returns an integer value to the caller that indicates the success or failure of the procedure execution.

• 0: Success.
• non-zero: Failure. The specific value indicates the type of error.
• NULL: If no integer expression is specified, a NULL value is returned.

Permissions

No special permissions are required. The permissions required are the same as for executing the batch or stored procedure.

Examples

Example 1: Returning a Success Code

CREATE PROCEDURE usp_ReturnSuccess
AS
BEGIN
    PRINT 'Procedure executed successfully.'
    RETURN 0; -- Indicate success
END;
GO

-- Execute the procedure and capture the return value
DECLARE @ReturnCode INT;
EXEC @ReturnCode = usp_ReturnSuccess;
SELECT @ReturnCode AS ReturnValue;
GO
            

Example 2: Returning a Specific Value

CREATE PROCEDURE usp_CalculateSum
    @num1 INT,
    @num2 INT
AS
BEGIN
    DECLARE @sum INT;
    SET @sum = @num1 + @num2;
    RETURN @sum; -- Return the calculated sum
END;
GO

-- Execute the procedure and capture the return value
DECLARE @Result INT;
EXEC @Result = usp_CalculateSum @num1 = 10, @num2 = 25;
SELECT @Result AS SumResult;
GO
            

Example 3: Returning from a Trigger

The RETURN statement in a trigger terminates the trigger's execution immediately. Any subsequent Transact-SQL statements in the trigger are not executed.

CREATE TRIGGER trg_PreventDelete
ON YourTable
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Delete operation is not allowed.';
    RETURN; -- Exit the trigger without performing the delete
END;
GO
            

See Also