Skip to main content

Managing transactions in TSQL

TSQL doesn't handle multiple transactions very well.  In fact it doesn't handle it at all.  So what do you do when you have nested stored procedures, all which require transaction handling?

TSQL has a built in function that will return the number of BEGIN TRANSACTION statements that have been called on the current connection.

@@TRANCOUNT

Using this you can choose whether to begin a transaction or not based on the value it returns.  If it returns a number greater than 0 then you are currently in a transaction.

However if it returns a 0 then there were no other transactions and you are free to start one.  The best way to do this is to set a variable that indicates if a transaction is needed, since when you start your new transaction and you try to check again when you need to do a rollback or commit, @@TRANCOUNT will not return 0 since you recently started a transaction.

Continue reading
  10425 Hits