title: SQL While Loop to retry Transaction until success
body: <p>I need to have a transaction, which when it fails, rolls back, and retries. </p>
  
  <p>Where's what I have so far:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Save]
  @MasterID as bigint
  
  AS
  
  BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  Declare @successtrans bit = 0
  
  While @successtrans = 0
      Begin -- while
          BEGIN TRY
              BEGIN TRANSACTION
  
                  DELETE SomeRecords WHERE ParentRecordID = @MasterID
  
                  INSERT INTO SomeRecords 
                  Select FieldOne, FieldTwo, FieldThree, ParentRecordID
                  FROM OtherRecords 
                  WHERE ParentRecordID = @MasterID
  
                  INSERT INTO AnotherTable
                  SELECT FieldA, FieldB, FieldC, FieldD
                  FROM MoreRecords
                  WHERE FieldD = @MasterID
  
              COMMIT TRANSACTION
              set @successtrans = 1
          END TRY
          BEGIN CATCH
  
                  IF(XACT_STATE()) = -1 -- Determine the state of the current transction (1 = committable, 0 = no active transaction (same as @@Trancount check), -1 = active, but uncommittable) only on severity 17 or higher
                  BEGIN
                      DECLARE @ErrorNumber bigint = ERROR_Number(),
                          @ErrorSeverity int = ERROR_SEVERITY(),
                          @ErrorState int = ERROR_STATE(),
                          @ErrorProcedure varchar(100) = ERROR_PROCEDURE(),
                          @ErrorLine int = ERROR_LINE(),
                          @ErrorMessage varchar(Max) = ERROR_MESSAGE()
                      IF @@TRANCOUNT &gt; 0
                          ROLLBACK TRANSACTION
                      INSERT INTO ErrorLog(ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDate)
                      VALUES(@ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage, GETDATE())
                  END -- if
          END CATCH
      End -- while
  END -- proc
  GO
  </code></pre>
  
  <p>The question is, will this work for what I'm doing? Logic says yes. But this is a methodology I need to implement across 10 or more stored procs, and I'm having trouble making it fail and repeat.  </p>