A lot of times, I write a stored procedure to do several things. More often than not, I might be doing mass inserts, updates or deletions. Now, I’m not your average suspenders-and-belt guy. No, I go one step further and insist on wearing an overall over my suspenders and my belt, thank you very much. So, when I have to do that kind of thing, that potentially could do brain damage to my beloved database and not-amusing my favorite clients, I insist that the stored procedure run in a transaction. So, here we go:

SQL Server Hosting

Great, we’re done, right! Easy as pie! Well, unfortunately, no.

Let’s say that we have this calling code:

and suppose something did go wrong. What will happen? The data won’t be modified, great. But we’d get something like this:

Huh? What is it talking about? We have a balancing BEGIN TRANSACTION and COMMIT/ROLLBACK in our calling code and in the procedure! Why am I getting this silly error?

Well, the truth is that for each BEGIN TRANSACTION we go into, it increments the @@TRANCOUNT by 1. Then for each COMMIT we execute, it decrements the @@TRANCOUNT by 1. BUT, when we get to the ROLLBACK, @@TRANCOUNT is set to zero, irrespective of what it was originally set to. So, the execution path is like this:

Now, here’s the annoying thing. This “error” is actually informational. It’s not a bona fide error. Unfortunately, when you execute the code via a passthrough query or via an ADO command object, it implicitly wraps a transaction and thus trips up that error which give you that annoying message. Maybe something really did go wrong but maybe you do want to rollback and not treat it as an error. This “informational” message only serve to obscure and confuse the underlying issue that prevents the stored procedure from doing what it needs to do.

So, what are we to do about that silly message? Well, we can work around this by revising how we handle our errors and transactions:

1) We specified that XACT_ABORT be on, which ensures that any kind of error will cause the transaction to be terminated. Without that, there may be some errors that won’t cause the transaction to stop and frankly, it’s not really cool. We don’t want to wrap things in a transaction under the promise “that it must all succeed together, even with one error!” Huh? No, I much prefer “it must all succeed together, without any kind of errors!”, thanks.

2) We used a local variable to check the @@TRANCOUNT. That way, we only wrap the process in a transaction if it’s not already in one. Otherwise, we can just be assured that we’re covered by the transaction initiated by the calling code.

3) Since we may be inside a transaction that was initiated by the calling code, we don’t want to tell the calling code, “yeah, we succeeded with errors handled.” This might let the calling code do more things that we might not really want it to do, if there was a problem. Therefore, to force the calling code to stop and abandon its own transaction, we need to throw the error back to the calling code, and that’s the purpose of the RAISERROR. (Note: for those on 2012 and newer, the recommended keyword is now THROW. However, 2012 and 2014 are too new so for those still toting along on 2005/2008/2008R2, the RAISERROR is quite serviceable.)

With the original error bubbled up to the calling code, that also rids us of that not-so-informational error message about @@TRANCOUNT count being off and let us deal with the actual error that caused the whole thing to fall to the floor. I’m looking for broom now.

Several people have come up with variants of this pattern. One such example which served as an inspiration for the pattern can be found at Stack Overflow.

Happy transacting!