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
  10209 Hits
WebSan Solutions is a Gold Certified Microsoft Partner

Multi-Award Winning

Gold Microsoft Partner

Microsoft Partner of the Year

Dynamics 365 Business Central

Microsoft Canada Impact Award

Business Applications

Microsoft Inner Circle

Business Applications
WebSan is a Microsoft Solutions Partner

© Copyright 2024 WebSan Solutions Inc.WEBSAN is a trademark of WebSan Solutions Inc. Registered in Canada and other countries.