MERGE in a nested TRY/CATCH won't roll back

• Pete Bishop

  SSCommitted

  Points: 1505

  More actions

  October 29, 2026 at 9:43 am

  #5080282

  I have a section of code similar to the following abstract:

  DECLARE
      @loop [int] = 0,
      @savepoint [varchar](4)
  
  BEGIN TRY
      BEGIN TRANSACTION _OUTER_
  
      WHILE (@loop < 10)
      BEGIN
          SELECT
              @loop = @loop + 1,
              @savepoint = '_' + CAST(@loop AS [varchar](2))
  
          BEGIN TRY
          BEGIN TRANSACTION
              SAVE TRANSACTION @savepoint
  
              MERGE ... -- This transaction works
  
              MERGE ... -- This transaction fails with "The MERGE statement attempted to UPDATE or DELETE the same row more than once." 
  
              COMMIT TRANSACTION @savepoint
          END TRY
          BEGIN CATCH
              -- INNER CATCH CODE
              IF @@TRANCOUNT <> 0
                  IF XACT_STATE() <> -1
                      ROLLBACK TRANSACTION @savepoint
                  ELSE
                      THROW;
          END CATCH
      END --loop
  
      COMMIT TRANSACTION _OUTER_
  END TRY
  BEGIN CATCH
      -- OUTER CATCH CODE
      IF @@TRANCOUNT <> 0
          IF XACT_STATE() <> -1
              ROLLBACK TRANSACTION _OUTER_
  END CATCH

  When this runs, the "INNER CATCH CODE" throws the error to the "OUTER CATCH CODE" rather than simply rolling back the two MERGE updates. Without the XACT_STATE() check, the code fails with "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction."

  My question is, why can't the MERGE be rolled back so that other work performed in the procedure can be retained? I need the outer transaction and try/catch due to other functionality of the code (single instance running) but the main data updates are occurring inside the loop and it's those I'd like to roll back.

  Thanks.