SQL Server Advanced Nested Transactions in Nested Procs

Hello Guys,

As many of you have probably already experienced the pain of having to deal with
multiple transactions in nested procedures, I overcame the problem with the following
solution so thought I’d share it with you guys!

As we have 2 procs: 1 Inner and 1 Outer( you can have as many as you can but the
principle is the same), you will be most likely calling the proc with cross-db
or just within the same db.

As you can see both procs start a new transaction. Now the tricky part is actually handling
the error. So in other words – we want to raise if an error occur in the inner proc,
and handle it in the outer proc( other wise an error will occur, and they are
especially horrific if you are using a merge!).

As you can probably see I am using a savepoint for the transaction ( in case part of the transaction
is cancelled – a good practice maybe if handling a merge statement where a
unique constraint may occur). More information on https://msdn.microsoft.com/en-GB/library/ms188378.aspx

I’ve also specified XACT_ABORT OFF in order to be able the handle the error in the inner transaction.

I also like naming my transactions <3.
Anyway, let me know guys if you’ve found it helpful, or should you have any questions.

May SQL be with you!


 SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.OuterProc @ID AS INT
AS
 BEGIN 

 SET NOCOUNT ON;
 SET XACT_ABORT OFF;
 DECLARE @TransactionCount INT;
 SET @TransactionCount = @@trancount;
 BEGIN TRY
 IF @TransactionCount = 0
 BEGIN TRANSACTION OuterProc;

 DECLARE @Table TABLE ( ID INT );

 IF EXISTS ( SELECT TOP 1
 1
 FROM sys.objects
 WHERE object_id = OBJECT_ID(N'dbo.innerProc')
 AND type IN ( N'P', N'PC' ) )
 BEGIN
 INSERT INTO @Table
 ( ID )
 EXECUTE dbo.InnerProc @ID = @ID;

 END;

 IF @TransactionCount = 0
 BEGIN
 COMMIT TRANSACTION OuterProc;
 END;
 END TRY

 BEGIN CATCH

 DECLARE @error INT ,
 @message VARCHAR(4000) ,
 @xstate INT;
 SELECT @error = ERROR_NUMBER() ,
 @message = ERROR_MESSAGE() ,
 @xstate = XACT_STATE();
 IF @xstate = -1
 ROLLBACK;
 IF @xstate = 1
 AND @TransactionCount = 0
 ROLLBACK;
 IF @xstate = 1
 AND @TransactionCount > 0
 ROLLBACK TRANSACTION OuterProc;

 --Insert into SQLErrorLog (you wanna catch the error don't you?

 DECLARE @ErrorMessage NVARCHAR(4000);
 DECLARE @ErrorSeverity INT;
 DECLARE @ErrorState INT;

 SELECT @ErrorMessage = ERROR_MESSAGE() ,
 @ErrorSeverity = ERROR_SEVERITY() ,
 @ErrorState = ERROR_STATE();

 RAISERROR (@ErrorMessage, -- Message text.
 @ErrorSeverity, -- Severity.
 @ErrorState -- State.
 );
 END CATCH;
 END;

 

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.InnerProc @ID AS INT
AS
 BEGIN
 SET NOCOUNT ON;
 SET XACT_ABORT OFF;
 DECLARE @TransactionCount INT;
 SET @TransactionCount = @@trancount;
 BEGIN TRY
 IF @TransactionCount ! = 0
 BEGIN TRANSACTION InnerProc;
 ELSE
 SAVE TRANSACTION InnerProc; 

 SELECT TOP 1
 NationalIDNumber
 FROM AdventureWorks2014.HumanResources.Employee
 WHERE BusinessEntityID = @ID;

 IF @TransactionCount != 0
 BEGIN
 COMMIT TRANSACTION InnerProc;
 END; 

 END TRY
 BEGIN CATCH
 --The only thing you have to do if it raises an error is to RAISE the Error
 DECLARE @ErrorMessage NVARCHAR(4000);
 DECLARE @ErrorSeverity INT;
 DECLARE @ErrorState INT;

 SELECT @ErrorMessage = ERROR_MESSAGE() ,
 @ErrorSeverity = ERROR_SEVERITY() ,
 @ErrorState = ERROR_STATE();

 RAISERROR (@ErrorMessage, -- Message text.
 @ErrorSeverity, -- Severity.
 @ErrorState -- State.
 );

 END CATCH;
 END;

 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s