title: How to replace a SQL While Loop with a Select
body: <p>We have a procedure that uses the below While..Loop to calculate the total number of Days and Weeks paid for absences in our PayRoll System:</p>
  
  <p><strong>EDITED</strong></p>
  
  <pre><code>Declare @Absences Table (slno int identity (1,1),AbsenceId int, ToDate datetime)
  
  INSERT INTO @Absences (AbsenceID,ToDate)
  Select  AbsenceID, AB.ToDate
  from    t_Absence AB with (nolock)
          Inner Join t_AbsenceCategory AB_CAT with (nolock) ON (AB.AbsenceCategoryID = AB_CAT.AbsenceCategoryID)
  where   (AB_CAT.IsSSP =1) 
          and ClientID = @ClientID
          and AB.FromDate &gt;= @SSPYearStart --D7830 SJH 21/10/2015
  order BY AB.ToDate desc
  
  Declare @AbsenceID INT, @iCtr INT, @maxRows int
  Declare @FromDate datetime
  
  SELECT @iCtr = 1, @maxRows = MAX(slno) FROM @Absences
  Select @SSPDaysPaid = 0, @SSPweeksPaid = 0, @QualifyingDaysInWeek = 0
  
  If IsNull(@maxRows,0) &gt; 0 select @FromDate = FromDate  from t_Absence where AbsenceID = (SELECT AbsenceID FROM  @Absences WHERE slno = 1)
  
  WHILE ( @ictr &lt;= @maxRows )
      BEGIN
  
          SELECT  @AbsenceID = AbsenceID
          FROM    @Absences
          WHERE   slno = @iCtr
          --Print @AbsenceID
  
          If Exists (Select TOP 1 1 from t_Absence where ToDate &gt; DATEADD(dd,-56, @FromDate))
              BEGIN
  
                  SELECT @SSPDaysPaid =  @SSPDaysPaid + IsNull(A.SSPDays,0),
                      @FromDate = A.FromDate 
                  from t_Absence A 
                  where A.AbsenceID = @AbsenceID 
  
                  print '@SSPDaysPaid=' + CAST(@SSPDaysPaid AS Varchar(3)) + ' in Absence ' + cast(@AbsenceID as varchar(6))
                                      DECLARE @Monday int, @Tuesday int, @Wednesday int, @Thursday int, @Friday int, @Saturday int, @Sunday int
                  SELECT @Monday = QD.Monday, @Tuesday = QD.Tuesday, @Wednesday =QD.Wednesday, @Thursday =QD.Thursday,
                           @Friday = QD.Friday, @Saturday = QD.Saturday, @Sunday = QD.Sunday
                  from t_PayrollEmployeeSSPQualifyingDays QD 
                      inner JOIN t_Absence A on A.ClientID = QD.ClientID and A.FromDate = QD.DateFrom AND A.ToDate = QD.DateTo
                      where A.ClientID = @ClientId
                  SET @QualifyingDaysInWeek = @Monday + @Tuesday + @Wednesday + @Thursday + @Friday + @Saturday + @Sunday
                  print '@QualifyingDaysInWeek = ' + cast(@QualifyingDaysInWeek as char(2))
              END
          SET @iCtr = @iCtr + 1
      END
      if @QualifyingDaysInWeek &lt;&gt; 0 Set @SSPWeeksPaid = @SSPDaysPaid/@QualifyingDaysInWeek Else Set @SSPWeeksPaid = 0
      print '@SSPWeeksPaid=' + cast(@SSPWeeksPaid as varchar(2))
      Select 
  
      BradfordFactor
      , CSPFDEntitlement
      , CSPHDEntitlement
      , CSPDaysTaken
      , HasContract
      , CSPFullDaysTaken
      , CSPHalfDaysTaken -- DevTask 112703 06/11/2012 SWB Start
      , IsNull(@SSPDaysPaid,0) as 'SSPDaysPaid'
      , IsNull(@SSPWeeksPaid,0) as 'SSPWeeksPaid'
  from 
      fn_GetEmployeeBradfordFactor(@ClientID,DEFAULT,0, DEFAULT) 
  END
  </code></pre>
  
  <p>As I need to find out this information for several different persons I will have to execute this stored proc and loop once for each client Id (<code>@ClientId</code>) identified in the calling procedure ...</p>
  
  <p>Is there an alternative to this loop and would it be worth it in terms of performance?</p>