title: Skip Batch in While Loop if 60 seconds elapsed
body: <p>I'm trying to use a WHILE loop to iterate through batches to match a Customer Reference to a data table. The SQL Query I produced gets the desired results so I'm looking to run this over a longer time period - hense the while loop and batches. </p>
  
  <p>The batches take roughly 2 seconds to insert another 10,000 rows into the table, the issue I am encountering means that with specific batches the process continues to run but with no inserts taking place. I have identified that this seems to be consistently down to specific Call_IDs in the batch where no inserts happen, but when run for the problematic record specifically that will insert.</p>
  
  <p>I am looking for a solution that will allow the process to run the INSERT INTO in the batches of 10,000 records, but will skip a batch if it takes longer than 10 seconds. I have tried to SET LOCK_TIMEOUT = 60000 but this doesn't seem to change anything which hints the issue is not lock related.</p>
  
  <p>I then plan on running the process for the skipped batches but with a much smaller increment so I can identify the records that cause the issues.</p>
  
  <p>FYI I'm dealing with roughly 4 million records.</p>
  
  <p>Thanks in Advance!</p>
  
  <pre><code>CREATE PROCEDURE dbo.USR_FN_CRM_AGREEMENTS_BATCHES
  AS
  
  DECLARE @BatchStart  INT;
  DECLARE @ResultCount INT;
  DECLARE @Increment   INT;
  DECLARE @BatchStartTime DATETIME;
  
  SET @Increment = 10000;
  SET @BatchStart = 2858110;
  SET @ResultCount = 1;
  SET @BatchStartTime = GETDATE()
  
  WHILE(@ResultCount &gt; 0)
      BEGIN
  
      SET LOCK_TIMEOUT 60000;
  
  
          WITH AccountReferences
               AS (SELECT ra.ID AS AccountReference, 
                          ra.MainAsset, 
                          cgct.Contact_ID, 
                          cgct.Group_Contact_ID, 
                          ra.AccountStartDate, 
                          ra.AccountEndDate
                   FROM SQLActiveH_Reporting.dbo.Contact_Group_Contacts_T cgct
                        LEFT JOIN SQLActiveH_Reporting.dbo.Rent_Accounts ra ON cgct.Group_ID = ra.ContactDatabaseReference
                  ),
               CRM_Calls
               AS (SELECT cct.Call_ID, 
                          cct.Call_Made_By_Group_Contact_ID, 
                          cct.Call_Relates_To_Group_Contact_ID, 
                          cct.Call_Relates_To_Asset_ID AS Asset_ID, 
                          cct.Entered_Actual_DT AS Call_Date_Time
                   FROM SQLActiveH_Reporting.dbo.CRM_Calls_T cct
                   WHERE cct.Call_ID &gt; @BatchStart
                         AND cct.Call_ID &lt;= @BatchStart + @Increment
                         AND cct.Call_ID NOT IN (2873237)
               ),
               DataPrep
               AS (
               SELECT CASE WHEN AR1.AccountReference IS NOT NULL THEN AR1.AccountReference
                               WHEN AR2.AccountReference IS NOT NULL THEN AR2.AccountReference
                               WHEN AR3.AccountReference IS NOT NULL THEN AR3.AccountReference
                               WHEN AR4.AccountReference IS NOT NULL THEN AR4.AccountReference
                               WHEN AR5.AccountReference IS NOT NULL THEN AR5.AccountReference
                               WHEN AR6.AccountReference IS NOT NULL THEN AR6.AccountReference
                               WHEN AR7.AccountReference IS NOT NULL THEN AR7.AccountReference
                               WHEN AR8.AccountReference IS NOT NULL THEN AR8.AccountReference
                               WHEN AR9.AccountReference IS NOT NULL THEN AR9.AccountReference
                               ELSE NULL
                          END AS AccountReference, 
                          CRM.Call_ID, 
                          CRM.Call_Made_By_Group_Contact_ID, 
                          CRM.Call_Relates_To_Group_Contact_ID, 
                          CRM.Asset_ID, 
                          CRM.Call_Date_Time, 
                          ROW_NUMBER() OVER(PARTITION BY CRM.Call_ID ORDER BY CRM.Call_Date_Time) AS SEQ
                   FROM CRM_Calls CRM
                        LEFT JOIN AccountReferences AR1 ON CRM.Call_Made_By_Group_Contact_ID = AR1.Group_Contact_ID AND CRM.Asset_ID = AR1.MainAsset
                                                           AND CRM.Call_Date_Time BETWEEN AR1.AccountStartDate AND AR1.AccountEndDate
                        LEFT JOIN AccountReferences AR2 ON CRM.Call_Made_By_Group_Contact_ID = AR2.Group_Contact_ID
                                                           AND CRM.Call_Date_Time BETWEEN AR2.AccountStartDate AND AR2.AccountEndDate
                        LEFT JOIN AccountReferences AR3 ON CRM.Call_Relates_To_Group_Contact_ID = AR3.Group_Contact_ID
                                                           AND CRM.Asset_ID = AR3.MainAsset
                                                           AND CRM.Call_Date_Time BETWEEN AR3.AccountStartDate AND AR3.AccountEndDate
                        LEFT JOIN AccountReferences AR4 ON CRM.Call_Relates_To_Group_Contact_ID = AR4.Group_Contact_ID
                                                           AND CRM.Call_Date_Time BETWEEN AR4.AccountStartDate AND AR4.AccountEndDate
                        LEFT JOIN AccountReferences AR5 ON CRM.Call_Made_By_Group_Contact_ID = AR5.Group_Contact_ID
                                                           AND CRM.Asset_ID = AR5.MainAsset
                        LEFT JOIN AccountReferences AR6 ON CRM.Call_Made_By_Group_Contact_ID = AR6.Group_Contact_ID
                        LEFT JOIN AccountReferences AR7 ON CRM.Call_Relates_To_Group_Contact_ID = AR7.Group_Contact_ID
                                                           AND CRM.Asset_ID = AR7.MainAsset
                        LEFT JOIN AccountReferences AR8 ON CRM.Call_Relates_To_Group_Contact_ID = AR8.Group_Contact_ID
                        LEFT JOIN AccountReferences AR9 ON CRM.Asset_ID = AR9.MainAsset
                                                           AND CRM.Call_Date_Time BETWEEN AR9.AccountStartDate AND AR9.AccountEndDate
                         )
               INSERT INTO Admin_DBA.dbo.CRM_Accounts
                      SELECT dp.AccountReference, 
                             dp.Call_ID, 
                             dp.Call_Made_By_Group_Contact_ID, 
                             dp.Call_Relates_To_Group_Contact_ID, 
                             dp.Asset_ID, 
                             dp.Call_Date_Time,
                             @BatchStart
                      FROM DataPrep dp
                      WHERE Seq = 1
                   ;
  
  
          SET @BatchStart = @BatchStart + @Increment;
          SET @ResultCount = @@ROWCOUNT;
  
      END;
  
  USE [Admin_DBA]
  GO
  
  /****** Object:  Table [dbo].[CRM_Accounts]    Script Date: 18/07/2019 
  09:21:11 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[CRM_Accounts](
      [AccountReference] [varchar](50) NULL,
      [Call_ID] [int] NOT NULL,
      [Call_Made_By_Group_Contact_ID] [int] NULL,
      [Call_Relates_To_Group_Contact_ID] [int] NULL,
      [Asset_ID] [int] NULL,
      [Call_Date_Time] [datetime] NOT NULL,
      [SEQ] [bigint] NULL
  ) ON [PRIMARY]
  GO
  </code></pre>