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 1737 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
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

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


x

You might be Interested in these Posts

C Program to find the G.C.D of a number using Recursive function

Problem Write a program in C to find the find the G.C.D of a number using recursion and display the ...

C Program to print a Half Pyramid using *

Problem Write a program in C to print Half pyramid using * as shown. * * * * * * * * * * ...

C Program to display Positive factors of a number

Problem Write a program in C to display all the positive factors of a number enter by the user. Ho...