Exit the Current Scope with a return value in SQL Server

Posted by AbundantCode

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
Tags: