Esther
4Penny.net
Points: 7100

7/2/2021 8:23:27 AM

Exit a SQL Batch with multiple GOs conditionally

I love my job. 

I know I keep saying that... but I really do. 

Today's SQL puzzler is this (if you think that you have a solution that is better PLEASE comment below):

I need a script that has multiple GOs, and if something goes wrong I need the ENTIRE script to cancel. 

So

declare @somethingiswrong int
set @somethingiswrong = 1
 
if @somethingiswrong = 1 begin
    --code needs to exit here
    print 'exiting code'
end
 
 
go
 
--this is a new batch. this should not execute
print 'i should not be here'
go

I went though a couple of iterations, including

raiserror('Oh no a fatal error', 20, -1) with log

Which was brutally heavy handed, and 

set noexec on

Which... was interesting, and it worked... but I wasn't sold. 

I ended up with this:

 

 

4Penny.net
Version: Unknown or N/A
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3