Sometime back came across a scenario where I needed to STOP or ABORT the execution of the next statements in the current batch and in the subsequent batches based on some condition. Experimented multiple approach but in most of the scenarios was able to stop the execution of the subsequent statements in the current batch, but not the statements after the go statement. Two options worked one was the RAISERROR and another was the use of the SET NOEXEC ON option. I would prefer using SET NOEXEC ON option compared to RAISERROR. This article explains the problem, failed solution approaches and the successful approaches with examples.
Let us first understand what problem I am speaking about here:
PRINT '-----FIRST Batch - Start--------' IF(1=1) RETURN -- Intention is to stop execution PRINT '-----FIRST Batch - End--------' GO PRINT '-----SECOND Batch--------' GO PRINT '-----THIRD Batch--------' GO
The intention with which I have put the RETURN statement on line no. 3 is to stop the execution of the next PRINT statement i.e. PRINT ‘—–FIRST Batch – End——–‘ and the subsequent PRINT statements PRINT ‘—–SECOND Batch——–‘ and PRINT ‘—–THIRD Batch——–‘. Let us see what is the result:
RESULT:
From the result it is clear that the RETURN statement was able to stop the execution of the PRINT statement in the current batch, but not able to stop the execution of the PRINT statements after the GO statement.
Let us see Failed attempts to solve this problem with RAISERROR/THROW
Example 1: Try to solve by raising an error using RAISERROR statement with severity level 16
PRINT '-----FIRST Batch - Begining--------' IF(1=1) RAISERROR('RAISERROR Approach',16,1) -- Intention is to stop execution PRINT '-----FIRST Batch - Ending--------' GO PRINT '-----SECOND Batch--------' GO PRINT '-----THIRD Batch--------' GO
RESULT:
Example 2: Try to solve by raising an error using THROW statement
PRINT '-----FIRST Batch - Begining--------' IF(1=1) Throw 50000, 'THROW Approach', 1 -- Intention is to stop execution PRINT '-----FIRST Batch - Ending--------' GO PRINT '-----SECOND Batch--------' GO PRINT '-----THIRD Batch--------' GO
RESULT:
From the above examples result it is clear that neither RETURN statement nor RAISERROR statement or THROW statement are able to stop the execution of the statements in the subsequent batches separated by the GO statement.
Let us see the prefered approach to solve this issue by using the SET NOEXEC ON option
PRINT '-----FIRST Batch - Begining--------' IF(1=1) SET NOEXEC ON -- Intention is to stop execution PRINT '-----FIRST Batch - Ending--------' GO PRINT '-----SECOND Batch--------' GO PRINT '-----THIRD Batch--------' GO SET NOEXEC OFF
RESULT:
From the result it is clear that after the execution of the SET NOEXEC ON statement at line 3, further statements are not executed.
You may be wondering why on line 10 I have written SET NOEXEC OFF. The reason for this is, the SET NOEXEC ON statement on line no. 3 instructs sql server to stop executing the statements after it in the current session. To reset this option for the current session we have to execute the SET NOEXEC OFF statement. Let us understand this with an example:
PRINT '-----FIRST Batch - Begining--------' IF(1=1) SET NOEXEC ON -- Intention is to stop execution PRINT '-----FIRST Batch - Ending--------' GO PRINT '-----SECOND Batch--------' GO PRINT '-----THIRD Batch--------' GO SET NOEXEC OFF GO PRINT '-----FOURTH Batch--------' GO
RESULT:
Note the statement SET NOEXEC ON causes sql server to stop executing the subsequent statement after it, but it compiles all the statements.
RAISERROR approach to solve this problem – Not Prefered
You may be wondering at the beginning of this article I have mentioned that RAISERROR can also be used to solve this problem. But one of the examples in this article shows that even RAISERROR can’t solve this issue. RAISERROR can solve this problem if we raise an error with severity >= 20 with LOG option. But only the User with SysAdmin rights can raise error with this severity and it also terminates the connection. Because of this reason, RAISERROR is not a preferred approach for me to solve this problem.
Please let me know your prefered approach for solving this problem and also correct me if my understanding is not correct.
Brilliant work, and thank you very much for showing what does NOT work as well as what does.
Thank you George
Nice article,and clear with all basic understading level..thanks..
Thank you Shanky Narang
Thank you for the article. I guess, in the example 2, on the 3 rd row is small error
Correct is : Throw 50000, ‘THROW Approach’, 1
Corrected. Thank you Martin for pointing out :).
Thanks,
Basavaraj
This definitely solves the problem, and one I am wrestling with.
However, one small problem is that if there is code in the 2nd or 3rd batch which generates an error, it is still interpreted and so that error is still printed even though the code doesn’t actually run. You might ask why I have an might have an error in the 2nd or 3rd batch? Well, that is why I am testing for a condition early on in order to stop the script from running.
I have a problem similar to what Robert is seeing, however the part of the script after the error runs.
For example, this works fine:
SELECT ‘Start the Process’
SET NOEXEC ON;
SELECT ‘First SELECT’
WHERE 1 = 1;
GO
SELECT ‘Second SELECT’
WHERE 1 = 1;
GO
SET NOEXEC OFF;
But this version not only gives me an error, it also runs the second SELECT statement:
SELECT ‘Start the Process’;
SET NOEXEC ON;
–SELECT ‘First SELECT’
WHERE 1 = 1;
GO
SELECT ‘Second SELECT’
WHERE 1 = 1;
GO
SET NOEXEC OFF;
Hi Gary
For your second script, please try this:
– put a GO after the SET NOEXEC ON;
– The error is preventing the exececution of SET NOEXEC ON
– The syntax error message will still appear
– but the second SELECT statement will not run
Hope that works
🙂
Manish
Thank you
Referring to last set noexec on example , noexec works all the time everywhere.
except it does not work if we are creating any SP which has table type parameter , may be some SQL server bug – not sure,
Thank you for making it so simple to understand. I came here for something else but learned something gr8.
Great Post! Thanks for sharing
Thanks for that SQL guru 🙂