Problem working with named transactions inside stored procedures
/*
**********************************************************
*/
CREATE PROCEDURE #WorkingWithNamedTransactionsOne
-- This procedure does not raise error
AS
DECLARE @ERROR bit
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
SET @ERROR = 1
PRINT 'NamedTransactionOne started'
-- SOME TSQL statement here
IF @@ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsOne_Finally
END
-- SOME TSQL statement here
IF @@ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsOne_Finally
END
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
SET @ERROR = 0
PRINT 'NamedTransactionOne committed'
WorkingWithNamedTransactionsOne_Finally:
IF @ERROR = 1
BEGIN
PRINT 'Rolling back NamedTransactionOne'
ROLLBACK TRANSACTION NamedTransactionOne
SET @ERROR = 0
PRINT 'NamedTransactionOne rolled back'
END
RETURN
GO
/*
**********************************************************
*/
CREATE PROCEDURE #WorkingWithNamedTransactionsTwo
-- This procedure raises error
AS
DECLARE @ERROR bit
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
SET @ERROR = 1
PRINT 'NamedTransactionOne started'
-- SOME TSQL statement here
IF @@ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsTwo_Finally
END
SELECT 1/0 AS [This might raise an error]
IF @@ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsTwo_Finally
END
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
SET @ERROR = 0
PRINT 'NamedTransactionOne committed'
WorkingWithNamedTransactionsTwo_Finally:
IF @ERROR = 1
BEGIN
PRINT 'Rolling back NamedTransactionOne'
ROLLBACK TRANSACTION NamedTransactionOne
SET @ERROR = 0
PRINT 'NamedTransactionOne rolled back'
END
RETURN
GO
but when I execute them using the following script, an error occurred on the fourth EXECUTE statement
/* ********************************************************** */ EXECUTE #WorkingWithNamedTransactionsOne GO EXECUTE #WorkingWithNamedTransactionsTwo GO BEGIN TRANSACTION EXECUTE #WorkingWithNamedTransactionsOne COMMIT TRANSACTION GO BEGIN TRANSACTION EXECUTE #WorkingWithNamedTransactionsTwo --ATTENTION: Server: Msg 6401, Level 16, State 1 - Cannot roll back NamedTransactionOne. No transaction or savepoint of that name was found. COMMIT TRANSACTION GO
How would you solve it? After dealing with this problem some time and doing some tests, I came out with the following new two stored procedures
/*
**********************************************************
*/
CREATE PROCEDURE #WorkingWithNamedTransactionsAndSavepointsOne
-- This procedure does not raise error
AS
DECLARE @ERROR int
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne started'
PRINT 'Starting NamedSavepointOne'
SAVE TRANSACTION NamedSavepointOne
PRINT 'NamedSavepointOne started'
-- SOME TSQL statement here
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsAndSavepointsOne_Finally
END
-- SOME TSQL statement here
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsAndSavepointsOne_Finally
END
WorkingWithNamedTransactionsAndSavepointsOne_Finally:
IF @ERROR <> 0
BEGIN
PRINT 'Rolling back NamedSavepointOne'
ROLLBACK TRANSACTION NamedSavepointOne
PRINT 'NamedSavepointOne rolled back'
END
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne committed'
RETURN
GO
/*
**********************************************************
*/
CREATE PROCEDURE #WorkingWithNamedTransactionsAndSavepointsTwo
-- This procedure raises error
AS
DECLARE @ERROR int
PRINT 'Starting NamedTransactionOne'
BEGIN TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne started'
PRINT 'Starting NamedSavepointOne'
SAVE TRANSACTION NamedSavepointOne
PRINT 'NamedSavepointOne started'
-- SOME TSQL statement here
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsAndSavepointsTwo_Finally
END
SELECT 1/0 AS [This might raise an error]
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
GOTO WorkingWithNamedTransactionsAndSavepointsTwo_Finally
END
WorkingWithNamedTransactionsAndSavepointsTwo_Finally:
IF @ERROR <> 0
BEGIN
PRINT 'Rolling back NamedSavepointOne'
ROLLBACK TRANSACTION NamedSavepointOne
PRINT 'NamedSavepointOne rolled back'
END
PRINT 'Committing NamedTransactionOne'
COMMIT TRANSACTION NamedTransactionOne
PRINT 'NamedTransactionOne committed'
RETURN
GO
Then I executed the new stored procedures again as follow, and no errors occurred
/* ********************************************************** */ EXECUTE #WorkingWithNamedTransactionsAndSavepointsOne GO EXECUTE #WorkingWithNamedTransactionsAndSavepointsTwo GO BEGIN TRANSACTION EXECUTE #WorkingWithNamedTransactionsAndSavepointsOne COMMIT TRANSACTION GO BEGIN TRANSACTION EXECUTE #WorkingWithNamedTransactionsAndSavepointsTwo COMMIT TRANSACTION GO
What do you think about it? If you came out with a different solution, let me know about.