Save Transaction on MSDN

In this post we look at Microsoft’s MSDN article on SAVE TRANSACTION. That article applies to SQL Server (starting with 2008) and Azure SQL Database.

Here is the code example, with a few minor modifications I made. I removed the input parameter because we don’t need it. The comments take up fewer lines. The name of the procedure has “MSDN” added to the end of it. I re-named the variable from @TranCounter to @TranCountAtStart to be consistent with the previous post. The actual work done in the procedure was changed from a DELETE…WHERE statement at MSDN to our three calls to our stored procedures. The second stored procedure will throw an error. We could test this by removing the error by commenting our the second call to the stored procedure to test this code when there is no errors.

In the code below, in our example, we do not have a previous transaction. Therefore @@TRANCOUNT will be zero and @TranCountAtStart will be zero.

All or Nothing

We want to run four statements inside the TRY block. They all run without error, or none of them run. That’s our business rule. However, we are mindful that a calling routine may have started their own transaction and then within that transaction, the calling routine called this procedure. If that is the case, @@TRANCOUNT will be greater than zero and we will save the transaction, so that if we get an error in this procedure, we do not roll back the transaction in the calling procedure. We will raise and error, however.

USE [SqlHintsErrorHandlingDemo];  
GO  
IF EXISTS (SELECT name FROM sys.objects  
           WHERE name = N'SaveTranExampleMSDN')  
    DROP PROCEDURE SaveTranExampleMSDN;  
GO  
CREATE PROCEDURE SaveTranExampleMSDN  
AS  
    -- Detect whether the procedure was called from an active transaction and save
    -- that for later use. In the procedure, @TranCounter = 0  
    -- means there was no active transaction and the procedure started one. 
    -- @TranCounter > 0 means an active transaction was started before the 
    -- procedure was called.  
    DECLARE @TranCountAtStart INT;  
    SET @TranCountAtStart = @@TRANCOUNT;  
    IF @TranCountAtStart > 0
		BEGIN  
			-- Procedure called when there is an active transaction. 
			-- Create a savepoint to be able to roll back only the work done  
			-- in the procedure if there is an error.
			SAVE TRANSACTION ProcedureSave;
			PRINT '@TranCountAtStart is greater than zero; save transaction.'  
        END
	ELSE  
		BEGIN
			-- Procedure must start its own transaction. 
			BEGIN TRANSACTION;
			PRINT '@TranCountAtStart is zero; begin transaction';
		END
	--  
    BEGIN TRY 
        -- Here is where you do your work 
		PRINT 'We are now inside the BEGIN TRY block.'
		DELETE FROM dbo.Account;
		EXEC InsertAccount1
		EXEC InsertAccountDup  -- will throw error!
		EXEC InsertAccount2
        -- Get here if no errors; must commit any transaction started in the 
        -- procedure, but not commit a transaction started before the transaction was called.
        IF @TranCountAtStart = 0  
            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called. 
            -- The procedure must commit the transaction it started. 
            COMMIT TRANSACTION;  
    END TRY
    -- ------------------------------------------------------------------------------------------  
    BEGIN CATCH 
        PRINT 'An error occurred. We are now inside the CATCH block.' 
        -- An error occurred; must determine which type of rollback will  
        -- roll back only the work done in the procedure. 
        IF @TranCountAtStart = 0  
            -- Transaction started in procedure. Roll back complete transaction. 
            ROLLBACK TRANSACTION;  
        ELSE  
            -- Transaction started before procedure called, do not roll back modifications 
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1  
                -- If the transaction is still valid, just roll back to the savepoint set at the 
                -- start of the stored procedure.  
                ROLLBACK TRANSACTION ProcedureSave;  
                -- If the transaction is uncommitable, a rollback to the save point is not allowed 
                -- because the save point rollback writes to the log. Just return to the caller, which 
                -- should roll back the outer transaction.  
        -- After the appropriate rollback, echo error information to the caller. 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH  
GO

Below is our output from the Results pane. We already know that the second call to execute a procedure, EXEC InsertAccountDup throws an error. Since this is all-or-nothing, we want to roll back everything inside our procedure.

@TranCountAtStart is zero; begin transaction
We are now inside the BEGIN TRY block.

(0 row(s) affected)
An error occurred. We are now inside the CATCH block.
Msg 50000, Level 14, State 1, Procedure SaveTranExampleMSDN, Line 69 [Batch Start Line 2]
Violation of PRIMARY KEY constraint 'PK__Account__349DA5A647432891'. Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (1).
Return Value
------------
-4

(1 row(s) affected)