How to replace a SQL While Loop with a Select

Question

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:

EDITED

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 >= @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) > 0 select @FromDate = FromDate  from t_Absence where AbsenceID = (SELECT AbsenceID FROM  @Absences WHERE slno = 1)

WHILE ( @ictr <= @maxRows )
    BEGIN

        SELECT  @AbsenceID = AbsenceID
        FROM    @Absences
        WHERE   slno = @iCtr
        --Print @AbsenceID

        If Exists (Select TOP 1 1 from t_Absence where ToDate > 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 <> 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

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 (@ClientId) identified in the calling procedure ...

Is there an alternative to this loop and would it be worth it in terms of performance?

it appears that the answer is 'no', the loop is retrieving absence information FOR EACH client. You must loop. unless you don't care about printing absences to the screen, then jut delete the loop. — Ricardo C
– Ricardo C, Commented Jun 10, 2016 at 15:56
@RicardoC : Well I don't need the prints (they're there for debugging and analysis) but I do need to sum the values @QualifyingDaysInWeek in for each record in the table @Absences — Our Man in Bananas
– Our Man in Bananas, Commented Jun 10, 2016 at 16:05
Why are you sometimes using NOLOCK and other times not using it for the same table??? You should probably read up about that hint before you litter your database with it. blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere — Sean Lange
– Sean Lange, Commented Jun 10, 2016 at 16:09
What is the point of this procedure? All you have is some print statements. Unless your user is running this in SSMS there won't be any output. I suspect you modified this quite a bit before posting. — Sean Lange
– Sean Lange, Commented Jun 10, 2016 at 16:12

Ricardo C · Accepted Answer · 2016-06-10 17:36:37Z

1

Based on the OP comment, no other value is needed from the loop, but @QualifyingDaysInWeek

@RicardoC : Well I don't need the prints (they're there for debugging and analysis) but I do need to sum the values @QualifyingDaysInWeek in for each record in the table @Absences

There appears to be no need for the loop at all.

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 >= @SSPYearStart --D7830 SJH 21/10/2015
order BY AB.ToDate desc

DECLARE @QualifyingDaysInWeek INT

 SELECT @QualifyingDaysInWeek = SUM(QD.Monday + QD.Tuesday + QD.Wednesday + QD.Thursday + QD.Friday + QD.Saturday + 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;

edited Jun 10, 2016 at 17:36

answered Jun 10, 2016 at 17:31

Ricardo C

2,26421 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Our Man in Bananas Over a year ago

Thanks for your help with this, I have edited my post to show the results that are passed back to the calling proc...

Collectives™ on Stack Overflow

How to replace a SQL While Loop with a Select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related