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.
RETURN [ integer_expression ]
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.
CREATE PROCEDURE get_Students_No
DECLARE @Count int
SELECT @Count = count(*) FROM Students
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.
DECLARE @return_value int
EXEC @return_value = get_Students_No;
SELECT 'Total students ' = @return_value
The following example shows that the RETURN statement can also be used to exit a block, so that the next block is not executed.
PRINT 'First block'
PRINT 'Next block'