Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one?
It seems like a bit of boilerplate code that I need to repeat everywhere and it will make for worse readability.
I'm trying to future proof against this error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Example of boilerplate code Exception handling and nested transactions
Best Answer
Unless you like typing it all out by hand, yes, you should use boiler plate code for transactions in procedures, if your need explicit transactions. The pattern I use to to wrap all my non-boiler plate code within the transaction in a BEGIN/END block, with a comment on the same line after the begin.
I also have a block before the transaction code for all setup/work that can be done outside a transaction. For example, suppose I need to create a transaction for all accounts in a given state. I may identify all accounts in a certain state and load them into a temp table. Then I build out the transaction, line items, etc., in to temp tables doing all the calculations, etc. outside the main transaction. Note: I do not care if someone if price changes during this process because the business would consider it valid as long as everyone in the list got the same pricing (either just before) or just after, the change.
When I go to insert the transactions and line items, I do all of that in the boiler plate to ensure that it is impossible to have a transaction without the corresponding line items. Also, I try to make more of my processes "trial-able"... you can try it in production, output what the results would look like, then rollback.
Keep in mind single statements have an implicit transaction, so no need to boiler plate if you are really just running one query. Or, if you are running multiple queries and a change between is acceptable for your particular case.
Lastly, for some cases, I also add internal testing as well, ensuring the state of things before I start, while in process, and a check at the end that we haven't arrived at an invalid state. A lot of this has to do with users using a flexible system, then doing something that breaks assumptions. These are generally high importance/infrequently run scripts (e.g. Billing). I also tend to run in trial mode just before production mode, to catch any issues first.
Because of that, my boiler plate also includes the code to deal with testing and for the trialing of results. I just delete it when I don't need it.