I wanted to use a template for writing procedures that behave as intuitively as possible in regard to nested transactions. My goals were:

• The procedure template should wrap all the work done in the procedure in a transaction.
• The procedures should be able to call each other and the calee should nest its transaction inside the outer caller transaction.
• The procedure should only rollback its own work in case of exception, if possible.
• The caller should be able to resume and continue even if the calee rolled back its work.

My solution is to use a either a transactions or a savepoint, depending on the value of @@TRANCOUNT at procedure start. The procedures start a new transaction if no transaction is pending. Otherwise they simply create a savepoint. On exit the procedure commits the transaction they started (if they started one), otherwise they simply exit. On exception, if the transaction is not doomed, the procedure either rolls back (if it started the transaction), or rolls back to the savepoint it created (if calee already provided a transaction).

Read the rest of this entry »