Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multi Step Update with full rollback if any fail (SQL2005 up)

T-SQL Hints and Tips

Multi Step Update with full rollback if any fail (SQL2005 up)

by  Qik3Coder  Posted    (Edited  )
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: 
[color green]--drop table #tmpTable[/color]
[color blue]create table[/color] #tmpTable(
intID [color blue]int identity[/color](1,1),
SomeText [color blue]varchar[/color](3))
[color green]--Load table[/color]
[color blue]insert into[/color] #tmpTable(SomeText) [color blue]Values[/color]([color red]'aaa'[/color])
[color blue]insert into[/color] #tmpTable(SomeText) [color blue]Values[/color]([color red]'bbb'[/color])
[color green]--Err Variables[/color]
[color blue]Declare[/color] @LastErrText [color blue]varchar[/color](4000), @LastErrNum [color blue]int[/color], @LastTranCount [color blue]int[/color]
[color blue]Select[/color] @LastErrText = [color red]''[/color], @LastErrNum = 0, @LastTranCount = 0

[color blue]begin try[/color]
[color green]--start transaction[/color]
[color blue]begin tran
 update[/color] #tmpTable
	[color blue]set[/color] SomeText = [color red]'zzz'[/color]
	[color blue]where[/color] SomeText = [color red]'aaa'[/color]

 [color green]--Force error to test logic[/color]
 [color blue]select[/color] 1/0

 [color blue]update[/color] #tmpTable
	[color blue]set[/color] SomeText = [color red]'www'[/color]
	[color blue]where[/color] someText=[color red]'bbb'[/color]
[color blue]end try
begin catch[/color]
	[color green]--save errors[/color]
	[color blue]Select[/color] @LastErrText = ERROR_MESSAGE(), 
	  @LastErrNum = Error_Number(), 
	  @LastTranCount = [color fuchsia]@@TranCount[/color]
	[color green]--undue any updates that worked[/color]
	[color blue]rollback[/color]
	[color green]--Optional view errors
		--Select @LastErrText , @LastErrNum , @LastTranCount
	--Throw an error[/color]
	[color blue]RaisError[/color](@LastErrText,16,1)
	[color green]--break out[/color]
	[color blue]RETURN
end catch[/color]
[color green]--Commit updates[/color]
[color blue]commit tran[/color]
[color green]--show new values[/color]
[color blue]select[/color] * [color blue]from[/color] #tmpTable
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top