Problem working with named transactions inside stored procedures
I created the following two stored procedures using named transactions inside them /*
**********************************************************
*/
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.