title: Looping a Delete SP
body: <p>I need to some help trying to create some handling for this SP I'm creating which currently looks like this...</p>
  
  <pre><code> alter PROCEDURE ArchiveCampaignData 
  
      @CampaignId INT,
      @DeleteBatchOf INT
  
       AS
  
  
  BEGIN
  
  BEGIN   
  
      DECLARE @Rowcount INT = 1
  
      WHILE @Rowcount &gt; 0
  
      DELETE TOP (@DeleteBatchOf)FROM dbo.u_activity
      OUTPUT DELETED.* INTO u_activity_archive
      WHERE campaignId = @CampaignId
  
      SET @Rowcount = @@ROWCOUNT
  END
  
  
  BEGIN   
  
      DECLARE @Rowcount2 INT = 1
      WHILE @Rowcount2 &gt; 0
  
          DELETE TOP (@DeleteBatchOf) C
          OUTPUT DELETED.* INTO dbo.u_contact_archive
          FROM dbo.U_contact AS C
               INNER JOIN dbo.U_Campaigncontact AS CON WITH(NOLOCK)ON C.id = CON.contactId
          WHERE CON.campaignId = @CampaignId
  
          SET @Rowcount2 = @@ROWCOUNT
  
  END
  
  BEGIN   
  
      DECLARE @Rowcount3 INT = 1
      WHILE @Rowcount3 &gt; 0
  
      DELETE TOP (@DeleteBatchOf) FROM u_campaignContact
      OUTPUT Deleted.* INTO u_campaignContact_archive
      WHERE campaignId = @CampaignId
  
      SET @Rowcount3 = @@ROWCOUNT
  
  END
  
  
  BEGIN   
  
      DECLARE @Rowcount4 INT = 1
      WHILE @Rowcount4 &gt; 0
  
          DELETE TOP (@DeleteBatchOf) L
          OUTPUT DELETED.* INTO dbo.dxi_CDRLog_Archive
          FROM dbo.dxi_CDRLog AS L
               INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)ON L.qid = H.diallerCampaignId
          WHERE H.parentId = @CampaignId
  
          SET @Rowcount4 = @@ROWCOUNT
  
  END
  
  
  BEGIN   
  
  
          DECLARE @Rowcount5 INT = 1
          WHILE @Rowcount5 &gt; 0
  
          DELETE TOP(@DeleteBatchOf) T
          OUTPUT Deleted.* INTO dxi_diallerStatsSmall_archive
          FROM dbo.dxi_diallerStatsSmall AS T
          INNER JOIN dbo.u_campaignHdr AS G WITH (NOLOCK) ON G.diallerCampaignId = T.campaign   
          WHERE G.id = @CampaignId
  
          SET @Rowcount5 = @@ROWCOUNT
  
   END
  
  BEGIN
  
          DECLARE @Rowcount6 INT = 1
          WHILE @Rowcount6 &gt; 0
  
          DELETE TOP(@DeleteBatchOf) FROM dbo.u_campaignHdr
          OUTPUT Deleted.* INTO u_campaignHdr_archive
          WHERE Id = @CampaignId OR parentId = @CampaignId
  
          SET @Rowcount6 = @@ROWCOUNT
  
  END
  END
  </code></pre>
  
  <p>the goal is for each block to loop in the SP to run at a time and once each block is completed that block will stop its transaction and it will move on to the next block as well as printing 'Completed' after each successful delete. </p>
  
  <p>Any recommendations/Enhancements will be appreciated . </p>