TIP: Nested Stored Procedure Calls with SQL Server Transactions, Page 2
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.