Error Handling in Transactions Using @@Error

Most people assume that if anything goes wrong in a transaction that the entire transaction will roll back. Sadly, that's not the case! In this video we demonstrate a common error where an insert fails due to a unique constraint, but the rest of the transaction succeeds, and then show you one way of fixing it using @@Error.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Varun on 1/15/2010

it was nice

AEE49878AC on 1/15/2010

Excellent. I was not aware of that.

Tracy on 1/15/2010

Very clear and just the right amount of information

Mark Weichman on 1/15/2010

hoping you cover best practices for error handling within an application i.e. capturing and reporting the full error message and the database, stored procedure and sql statement caused the error and how to report key data elements i.e. if you are processing a cursor of 1000 rows or an input file, which occurance caused the problem. Finally, should the message to the the event log and/or back to the app etc

Dennis Allen on 1/15/2010

Important subject. I am looking forward to more and details on using the new ERROR_ functions in SQL 2008. Something that I do in my critical procedures is to both log the error information to a table, adding line and state information, but then re-throw an error to be captured by the calling code and setting the return value to the error log id. If you would like details contact me at dennis_d_allen@yahoo.com Wonderful as always! Thanks.

Max Westerfield on 1/15/2010

Very good topic and well done.

Thom Bolin on 1/15/2010

Great knowledge been doing this for a long time, when I code I check for @@Error to return errors and rollback. But this is a simple way to show why it is so important.

Ed K on 1/15/2010

Great information to have, but it brings up a very distressing question. What else can cause this situation? Can you direct us to documentation as to what types of errors do NOT trigger ROLLBACK ?

Mark Hions on 1/15/2010

Don't highlight code unless you have to. Change the colours for selected text to black on a yellow background. Black on blue is almost invisible. I hope BEGIN TRY is next, as well as XACT_ABORT

Leonard Peoples on 1/15/2010

Very nice.

Thomas Kaiser on 1/15/2010

Excellent informatino

Susan McGlasson on 1/15/2010

Great topic!

Crystal on 1/15/2010

Very easy to understand - clear and concise instructions.

Steve Harris on 1/15/2010

more explanation of what @@trancount is and does

luther smith on 1/15/2010

Good to know!

995751D667 on 1/15/2010

Wow! That was good information. Would also like to know what try/catch does in this situation.

malik on 1/16/2010

Great info. Thank you!

Tim on 1/18/2010

Very useful and one to watch out for.

Travis on 1/20/2010

INCLUDE CODE!!!!!!!

Rene Rodriguez on 2/1/2010

Excellent speaker!

Sam Schafer on 2/18/2010

I've seen lots of folks that don't really "have" this concept. They think once in a transaction, changes are bulletproof. Andy makes a very good point here.

Rudy on 5/27/2010

great video

Lamar Marsengill on 7/6/2010

Awesome information....Thanks!

Sudhir on 8/3/2011

Excellent example. Thanks

Teresa Castleberry on 3/25/2013

Andy is great. Clear and concise, very easy to understand. Thanks, Andy!