INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Multi Step Update with full rollback if any fail (SQL2005 up) by Qik3Coder
Posted: 27 Jun 08

I had an issue today where I was updating part of a table in separate update statements but needed the entire thing to rollback if any of it failed. I started looking at nested begin trans and checking my err codes and found a new construct that works amazingly well for this.

The begin try and catch with a new function Error_Message() which allows you to actually grab the error text that is thrown.

CODE

--drop table #tmpTable
create table #tmpTable(
intID int identity(1,1),
SomeText varchar(3))
--Load table
insert into #tmpTable(SomeText) Values('aaa')
insert into #tmpTable(SomeText) Values('bbb')
--Err Variables
Declare @LastErrText varchar(4000), @LastErrNum int, @LastTranCount int
Select @LastErrText = '', @LastErrNum = 0, @LastTranCount = 0

begin try
--start transaction
begin tran
 update
#tmpTable
    set SomeText = 'zzz'
    where SomeText = 'aaa'

 --Force error to test logic
 select 1/0

 update #tmpTable
    set SomeText = 'www'
    where someText='bbb'
end try
begin catch

    --save errors
    Select @LastErrText = ERROR_MESSAGE(),
      @LastErrNum = Error_Number(),
      @LastTranCount = @@TranCount
    --undue any updates that worked
    rollback
    --Optional view errors
        --Select @LastErrText , @LastErrNum , @LastTranCount
    --Throw an error

    RaisError(@LastErrText,16,1)
    --break out
    RETURN
end catch

--Commit updates
commit tran
--show new values
select * from #tmpTable

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close