CODEWITHSUNDEEP
sql-serverstored-procedures
Yagiz Ozturk
Yagiz Ozturk

Reputation: 5428

How to stop the execution of a Stored Procedure using SQL Server?

Lets say I have a stored procedure which has a simple IF block. If the performed check meets the criteria, then I want to stop the procedure from further execution.

What is the best way to do this?

Here is the code:

IF EXISTS (<Preform your Check>)
BEGIN
    // NEED TO STOP STORED PROCEDURE EXECUTION
END
ELSE
BEGIN
    INSERT ()...
END

Thanks for any help with this!

Upvotes: 32

Views: 83628

Answers (2)

marc_s
marc_s

Reputation: 754258

Just make a call to RETURN:

IF EXISTS (<some condition>)
BEGIN
    // NEED TO STOP STORED PROCEDURE EXECUTION
    RETURN
END

This will return the control back to the caller immediately - it skips everything else in the proc.

Upvotes: 78

tdammers
tdammers

Reputation: 20721

You could simply put a jump label at the end of the SP body, and issue a GOTO in the first IF statement. Alternatively, you can extend the first BEGIN ... END block to contain the entire rest of the SP body, and reverse the condition (IF NOT EXISTS...).

Upvotes: 0

Related Questions