SQL Server: Stored Procedures and Transactions

Overview

It is the responsibility of a stored procedure to manage the transaction context of the session.  An error can result when the transaction depth on entry and exit from a stored procedure do not match.  This may occur when a transaction is rolled back in a stored procedure due to an error.

There are two ways to resolve this.  The first is to rely on the calling session to manage transactions appropriately and the stored procedure does not issue any commit or rollback statements.  This also means that the stored procedure should not be used outside of a transaction if the whole stored procedure should operate as if in a transaction.

The alternative approach is that the stored procedure should manage transaction state.  This article describes this process.

Procedural overview

The stored procedure starts by examining the transaction depth.  It can do this using the @@TRANCOUNT SQL variable.  If necessary, it starts a transaction.

Next it processes whatever statements are needed.  It can either use error testing logic or structured exceptions.

At the end of the stored procedure, the transaction context is restored.  If necessary new transactions are closed.  If a new transaction is needed it is started.

Example code

CREATE PROCEDURE [dbo].[usp_Name]
    @parameter1 int,
    @ERROR_MESSAGE varchar(50) out
AS
    DECLARE @startTransactionCount int
    DECLARE @error int

BEGIN 

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;-- Configure starting error state

-- Init error state
SET @error = 0
SET @ERROR_MESSAGE = 'Success'


-- Save the transaction count
SET @startTransactionCount = @@TRANCOUNT


-- If not already in a transaction, start one
IF (@startTransactionCount = 0)
    BEGIN TRANSACTION 

 
----------------------------------------------
-- Do stored procedure work here 
----------------------------------------------



-- Clean up transaction levels. This is necessary in the case
-- that a rollback has taken place
WHILE (@@TRANCOUNT > @startTransactionCount)
    BEGIN
        IF (@error = 0)
            COMMIT
        ELSE
            ROLLBACK 
    END

WHILE (@@TRANCOUNT < @startTransactionCount)
    BEGIN TRANSACTION 
 

-- Return any error condition to calling context 
RETURN @error 


END

Versions

• Tested with SQL Server 2005
• Tested with .NET Framework 2.0

Metadata

• Technorati Tags: SQL, SQL Server, stored procedures, software development, transactions, best practice