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