January 17, 2021
Hot Topics:

TIP: Nested Stored Procedure Calls with SQL Server Transactions

  • By Michael Klaene
  • Send Email »
  • More Articles »

The logic surrounding 'BEGIN TRANSACTION' interrogates the value of @@TRANCOUNT. If there already is an open transaction, it does nothing. The subsequent commit and rollback statements will execute only if the current procedure started the transaction. In other words, the 'COMMIT TRANSACTION' or 'ROLLBACK TRANSACTION' can only execute if the 'BEGIN TRANSACTION' was invoked in the same procedure. The key principal is this: If you didn't start the transaction, you have no business managing it. Simply leave it alone and return that responsibility to the caller. The caller, in turn, will either deal with it or in turn pass it up further, and so on. There are a number of different ways to achieve the same goal. It is probably a good idea to write a separate function that can encapsulate some of this routine logic so it can be reused throughout your application.

About the Author

Michael Klaene is a Principal Consultant with Sogeti USA. He has over 10 years of experience in IT, and specializes in J2EE, .NET, and Oracle design and development.

Page 2 of 2

This article was originally published on September 2, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date