TIP: Nested Stored Procedure Calls with SQL Server Transactions
When developing data-driven software, it is often vital that you manage transactions to ensure that unexpected errors do not corrupt the database. Transactions allow you to group sets of related database calls in a single, logical unit-of-work. The classic example of debiting one bank account, then crediting another, is often used to illustrate the importance of transactions. If the process blows up somewhere in the middle, you want everything put back as it was initially. It is the 'all or nothing' principle.
For Microsoft developers who write applications against a SQL Server 2005 database, it is common to write transaction logic in C#, using the System.Data.SqlClient.SqlTransaction class that wraps calls to SQL Server statements or stored procedures. Another option is to utilize MTS (Microsoft Transaction Server) to support distributed transactions. When these approaches are used, writing T-SQL code to manage transactions typically can be avoided. Sometimes, however, it makes the most sense to manage transactions at the stored procedure level. For example, you might choose to implement a process that requires numerous, process-intensive queries and data manipulation statements as a database stored procedure or a set of procedures. This tip will show you how to avoid transaction-related errors when nesting procedure calls in SQL Server.
In its simplest form, here is a SQL Server stored procedure that manages a transaction:
CREATE PROCEDURE [dbo].[simple_proc] AS BEGIN BEGIN TRY BEGIN TRANSACTION; PRINT 'Executing simple proc.' --Execute logic within transaction... COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; RAISERROR ('Error in simple proc!',16,1) END CATCH END
A transaction is started. If things go well, changes are committed in the database. Otherwise, the changes are rolled back. This works fine in most situations. That is, unless you have an environment in which you have other stored procedures that invoke this one and they too manage transactions. In such an environment, problems can arise because, though each 'BEGIN TRANSACTION' increments @@TRANCOUNT by one and each 'COMMIT TRANSACTION' decrements the count, a 'ROLLBACK TRANSACTION' rolls back all changes. That is, a rollback sets @@TRANCOUNT all the way back to zero. Attempting to execute a second rollback will generate an error with the message 'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.'.
This situation can occur if 'simple_proc' invokes a second procedure, 'another_simple_proc', but 'another_simple_proc' can also be called on its own. To account for the later scenario, 'another_simple_proc' must manage its own transactions instead of letting a caller do it for him. Because, in reality, it is not feasible to always know which procedure will be the outermost procedure in the chain, a certain amount of flexibility is necessary. So, some logic must be written to deal with this.
Next is an updated version of simple proc with some additional logic.
CREATE PROCEDURE [dbo].[simple_proc] AS BEGIN --Holds info on whether or not this proc originated the --transaction. DECLARE @v_trans_started BIT BEGIN TRY PRINT 'Executing simple proc.' SET @v_trans_started = 0 IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @v_trans_started = 1 END ELSE SET @v_trans_started = 0 --Execute logic within transaction... IF @v_trans_started = 1 BEGIN SET @v_trans_started = 0 COMMIT TRANSACTION; END END TRY BEGIN CATCH IF @v_trans_started = 1 BEGIN SET @v_trans_started = 0 ROLLBACK TRANSACTION; END RAISERROR ('Error in simple proc!',16,1) END CATCH END