Here is a somewhat kludgy way to do it that works with GO-batches, by using a "global" variable.
if object_id('tempdb..#vars') is not null
begin
drop table #vars
end
create table #vars (continueScript bit)
set nocount on
insert #vars values (1)
set nocount off
-- Start of first batch
if ((select continueScript from #vars)=1) begin
print '1'
-- Conditionally terminate entire script
if (1=1) begin
set nocount on
update #vars set continueScript=0
set nocount off
return
end
end
go
-- Start of second batch
if ((select continueScript from #vars)=1) begin
print '2'
end
go
And here is the same idea used with a transaction and a try/catch block for each GO-batch. You can try to change the various conditions and/or let it generate an error (divide by 0, see comments) to test how it behaves:
if object_id('tempdb..#vars') is not null
begin
drop table #vars
end
create table #vars (continueScript bit)
set nocount on
insert #vars values (1)
set nocount off
begin transaction;
-- Batch 1 starts here
if ((select continueScript from #vars)=1) begin
begin try
print 'batch 1 starts'
if (1=0) begin
print 'Script is terminating because of special condition 1.'
set nocount on
update #vars set continueScript=0
set nocount off
return
end
print 'batch 1 in the middle of its progress'
if (1=0) begin
print 'Script is terminating because of special condition 2.'
set nocount on
update #vars set continueScript=0
set nocount off
return
end
set nocount on
-- use 1/0 to generate an exception here
select 1/1 as test
set nocount off
end try
begin catch
set nocount on
select
error_number() as errornumber
,error_severity() as errorseverity
,error_state() as errorstate
,error_procedure() as errorprocedure
,error_line() as errorline
,error_message() as errormessage;
print 'Script is terminating because of error.'
update #vars set continueScript=0
set nocount off
return
end catch;
end
go
-- Batch 2 starts here
if ((select continueScript from #vars)=1) begin
begin try
print 'batch 2 starts'
if (1=0) begin
print 'Script is terminating because of special condition 1.'
set nocount on
update #vars set continueScript=0
set nocount off
return
end
print 'batch 2 in the middle of its progress'
if (1=0) begin
print 'Script is terminating because of special condition 2.'
set nocount on
update #vars set continueScript=0
set nocount off
return
end
set nocount on
-- use 1/0 to generate an exception here
select 1/1 as test
set nocount off
end try
begin catch
set nocount on
select
error_number() as errornumber
,error_severity() as errorseverity
,error_state() as errorstate
,error_procedure() as errorprocedure
,error_line() as errorline
,error_message() as errormessage;
print 'Script is terminating because of error.'
update #vars set continueScript=0
set nocount off
return
end catch;
end
go
if @@trancount > 0 begin
if ((select continueScript from #vars)=1) begin
commit transaction
print 'transaction committed'
end else begin
rollback transaction;
print 'transaction rolled back'
end
end