Reputation: 145
I have multiple layers of nested stored procedures, each with a try...catch
block within them. However, I want each of these nested stored procedures to be able to properly run their own transaction if they're called independently. To manage this, I've been frequently referencing this excellent article.
Now I am working on the error handling piece. If an inner TRY fails, I want it pass the error up to the outermost stored procedure (if one exists) and rollback the entire transaction. In the above mentioned article, the author handles this with an output parameter (@pResCode
).
I'm wondering: is there a reason you couldn't instead use the THROW()
function in the CATCH
block of the inner stored procedure? It seems like a simpler option.
Please reference the article to see his pattern for nested transactions. Below is what I am thinking. Note that the following stored procedures are effectively the same besides the first demonstrating what it looks like to call an inner stored procedure whereas the second does not.
Outer stored procedure:
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
-- Causes run-time errors to rollback the entire transaction
SET XACT_ABORT ON;
-- Create variable to indicate if SP is nested
DECLARE @IsNested BIT
-- Begin try block
BEGIN TRY
-- If SP is not nested, begin an explicit transaction and set nested
-- variable to false. If it is nested, set nested variable to true.
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
SET @IsNested = 0
END
ELSE
SET @IsNested = 1
-- Call second stored procedure
EXEC Procedure2
-- Maybe do other stuff
-- If stored procedure is not nested, commit the transaction
IF @IsNested = 0
BEGIN
SELECT 'Success' 'DbMsg'
COMMIT TRANSACTION
END
-- End try block
END TRY
-- Begin catch block
BEGIN CATCH
-- If stored procedure is not nested, rollback the transaction.
-- If the stored procedure is nested, throw an error to the outer stored procedure.
IF @IsNested = 0
BEGIN
SELECT ERROR_MESSAGE() 'DbMsg'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
DECLARE @ErrMsg nvarchar(4000) = ERROR_MESSAGE();
THROW 50000, @ErrMsg, 1
END
-- End catch block
END CATCH
END
Inner stored procedure:
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
-- Causes run-time errors to rollback the entire transaction
SET XACT_ABORT ON;
-- Create variable to indicate if SP is nested
DECLARE @IsNested BIT
-- Begin try block
BEGIN TRY
-- If stored procedure is not nested, begin an explicit transaction and set nested
-- variable to false. If it is nested, set nested variable to true.
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
SET @IsNested = 0
END
ELSE
SET @IsNested = 1
-- Do stuff
-- If stored procedure is not nested, commit the transaction
IF @IsNested = 0
BEGIN
SELECT 'Success' 'DbMsg'
COMMIT TRANSACTION
END
-- End try block
END TRY
-- Begin catch block
BEGIN CATCH
-- If stored procedure is not nested, rollback the transaction.
-- If the stored procedure is nested, throw an error to the outer stored procedure.
IF @IsNested = 0
BEGIN
SELECT ERROR_MESSAGE() 'DbMsg'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
DECLARE @ErrMsg nvarchar(4000) = ERROR_MESSAGE();
THROW 50000, @ErrMsg, 1
END
-- End catch block
END CATCH
END
Upvotes: 0
Views: 70