Exit the Current Scope with a return value in SQL Server

Published on August 25, 2016 by abundantcode

Filed under SQL Server

Last modified August 25, 2016

Print this page

rate 1 star rate 2 star rate 3 star rate 4 star rate 5 star
Your rating: none, Average: 0 (0 votes)

This article have been viewed 339 times

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
IF NOT EXISTS (SELECT BusinessEntityID     
                FROM HumanResources.Employee   
                 WHERE MaritalStatus = 'D')  
    RETURN -1;  
SELECT *  FROM HumanResources.Employee   WHERE  MaritalStatus = 'D';  

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment


You might be Interested in these Posts

Displaying all the worksheet names in Excel using VBA Code

Do you want to display the names of the worksheets in the excel workbook ?. Here;s how you can do it...

How to change positive numbers to negative in Microsoft Excel 2016 ?

Do you want to change the positive numbers to negative numbers and vice versa (or in simple terms) ,...

How to convert number to text in Excel?

In Microsoft Excel 2016 , there are times when you don't want the numbers present in the cell to be ...