You can use the RETURN statement to discontinue the execution of a T-SQL batch statement or a stored procedure and provide a status code or value on return.
For example , you want to display the Employees whose MaritalStatus is Divorced. You want to return a value -1 to indicate that no records exist and also You do not want the SQL Statements following it to be executed if this condition does not match. Additionally , you want to return 0 to indicate that there were records matching the criteria. You can use the IF NOT EXISTS and use the RETURN statement as shown in this example.
Exit the Current Scope with a return value in SQL Server
use AdventureWorks2014 GO CREATE PROCEDURE EmployeeData AS IF NOT EXISTS (SELECT BusinessEntityID FROM HumanResources.Employee WHERE MaritalStatus = 'D') BEGIN RETURN -1; END; SELECT * FROM HumanResources.Employee WHERE MaritalStatus = 'D'; RETURN 0