The RETURN statement can be used in a procedure to return values, it can also be used to end the execution of a sql block.

Syntax

RETURN [ integer_expression ]

RETURN value

In this first example, we will show how to use the RETURN statement inside a procedure. In the created procedure, a @Count variable was declared which will be populated based on a select in the students table, then with the help of the RETURN statement the value from the @Count variable is returned.

USE model
GO
CREATE PROCEDURE get_Students_No
AS
BEGIN
DECLARE @Count int
SELECT @Count = count(*) FROM Students
RETURN @Count
END
GO

The next step is to call the created procedure get_Students_No. The variable @return_value of type int is declared, then we populate it with the value returned by the procedure.

USE model
GO
DECLARE @return_value int
EXEC @return_value = get_Students_No;
SELECT 'Total students ' = @return_value
GO

RETURN exit

The following example shows that the RETURN statement can also be used to exit a block, so that the next block is not executed.

USE model
GO
BEGIN
PRINT 'First block'
RETURN
PRINT 'Next block'
END;