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.