CODEWITHSUNDEEP
sql-serversql-server-2005transactions
Jeff
Jeff

Reputation: 36573

Rollback Transaction on Errors

I have a large generated script. It has multiple batches (GO statements). The first line is BEGIN TRAN

If any errors occur during execution, either raised myself using RAISERROR, or raised by SQL Server, I want to rollback the transaction. I want the script to continue executing until the very end, then rollback, not abort execution as soon as any error occurs.

Checking @@error <> 0 at the end of the script doesn't seem to be sufficient, because if the last statement in the script succeeds, @@error will be 0, even though previous statements failed.

I can't declare a @rollback BIT at the very beginning because the script is segmented into multiple batches, and so the variable is out of scope.

I understand that RAISERROR doesn't mean my transaction will be rolled back.

Any suggestions on the best way to implement this in a SQL 2000 compatible manner?

Upvotes: 2

Views: 2426

Answers (2)

Clint Good
Clint Good

Reputation: 850

A common technique is to create a temporary table and use boilerplate code at the end of each batch

begin tran
go

create table #errors (errorid int not null)
go

select 1/ 0

declare @error int
set @error = @@error
if @error <> 0 insert into #errors values (@error)  
go

select 1/ 1

declare @error int
set @error = @@error
if @error <> 0 insert into #errors values (@error)    
go

if exists (select * from #errors)
begin
    print 'rolling back transaction'
    select * from #errors
    rollback tran
end
else
begin
    print 'commit transaction'
    commit tran
end
go

if OBJECT_ID('tempdb..#errors') is not null
    drop table #errors
go

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294237

You can always use SET CONTEXT_INFO to pass 'out-of-band' information, and read it later using CONTEXT_INFO(). If an error occurs, set the context info to a value which you check just before commit.

This answers your question of how to do it, but I really have to express my doubts about why do it in the first place. For one many errors will abort the transaction anyway so you may blindly proceed believing that is safe to continue and rollback only to discover that there is nothing to rollback and all your erroneous script after the exception has committed. Second, for the exceptions than do not abort the transaction, is still very questionable to do any work only to roll it back. Your request is so out of the beaten path than I must wonder if you really understand all the implications of what you're doing.

Upvotes: 2

Related Questions