dcsimg
June 18, 2018
Hot Topics:

TIP: Nested Stored Procedure Calls with SQL Server Transactions

  • September 2, 2008
  • 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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date