MSSQL Transaction Handling in Nested Stored Procedures

In the project I'm working on, we have a lot of transactions in the the stored procedures themselves; individual stored procedure will begin a transaction and either commit it or roll it back. One issue with this practice is calling several stored procedures within one transaction, initiated in another stored procedure or in the DAL. It doesn't really work if you just BEGIN and COMMIT/ROLLBACK transactions. You can nest transactions. And it works great if everything commits. Each BEGIN TRANSACTION just increase the number of COMMITs necessary to actually commit the transaction.

The problem is with rollbacks. A ROLLBACK rolls back to the outermost transaction. Not a terrible thing by itself, but it also causes exceptions to be thrown in the stored procedures. If a stored procedure exits and has a different value for @@TRANCOUNT (which represents the depth of transaction nesting) than when it started, SQL raises an error 266.

Of course, there are ways to deal with this. I found a very good article on CoDe Magazine web site describing a couple different methods of handling this. I worked on a project in the past where we used the Single-Level Model described in the article, and it worked well. The article has a good description of the problems and the two proposed ways to handle it. I would definitely recommend it.

While researching the SQL 266 error, I also ran across another good SQL reference site at http://www.sommarskog.se/. There are sections on dynamic SQL, arrays and lists, implementing error handling in stored procedures, sharing data between stored procedures, and lots of other good stuff.