How to replace nested cursors?

Question

I'm in the middle of writing an achievement module for a website we run. This particular bit of code will run on SQL Server at the end of a given timeframe to award the achievements (which will be notified through the client the next time the site is visited).

So I need to look at all the teams and all the "end" achievements (which are independent of the teams). Each achievement has one or more rules that must be met. Once I've determined that the achievement is passed, I award the achievement to all of the participants of that team.

I was handling this through cursors, and I got an error, so when I went to google the problem, I got endless links on forums of "YOU DUMB $&#$ WHY ARE YOU USING CURSORS" (to paraphrase). I figured while I was solving my problem, I may as well replace what I have using a set-based approach (if possible).

The alternative examples I found were all basic update scenarios using single nested loops on tables that have keys back to each other, and honestly, I wouldn't have considered using cursors in those scenarios to begin with.

What I have below is not entirely syntactically correct, but I can figure that out on my own by playing around. This should give a clear idea of what I'm trying to accomplish, however:

declare @TimeframeID int;
declare @AchievementID int;
declare @q1 int;
declare @q2 int;
declare @TeamID int;
declare @TotalReg int;  
declare @TotalMin int;
declare @AvgMin decimal(10, 2);
declare @AggType varchar(50);
declare @Pass bit = 1;
declare @Email varchar(50);
declare @ParticipantID int;

select @TimeframeID = MAX(TimeframeID) from Timeframes 
where IsActive = 1;

declare team_cur CURSOR FOR
select 
    t.TeamID, 
    (select COUNT(1) from Registrations r 
        where r.TeamID = t.TeamID and r.TimeframeID = @TimeframeID) TotalReg,
    (select SUM(Minutes) from Activities a 
        inner join Registrations r on r.RegistrationID = a.RegistrationID
        where r.TeamID = t.TeamID and r.TimeframeID = @TimeframeID) TotalMin
from Teams t
where Active = 1
group by TeamID;

declare ach_cur CURSOR FOR
        select AchievementID from luAchievements
        where TimeframeID = @TimeframeID and AchievementType = 'End';

declare rule_cur CURSOR for
        select Quantity1, Quantity2, AggregateType
        from AchievementRule_Links arl
        inner join luAchievementRules ar on ar.RuleID = arl.RuleID 
        where arl.AchievementID = @AchievementID;

open team_cur;

fetch next from team_cur
into @TeamID, @TotalReg, @TotalMin;

while @@FETCH_STATUS = 0
begin

    open ach_cur;

    fetch next from ach_cur
    into @AchievementID;

    while @@FETCH_STATUS = 0
        begin
            open rule_cur;

            fetch next from rule_cur
            into @q1, @q2, @AggType;

            while @@FETCH_STATUS = 0
                begin
                    if @AggType = 'Total'
                    begin
                        if @q1 > @TotalReg 
                        begin
                            set @Pass = 0;
                        end
                    end
                    else if @AggType = 'Average'
                    begin
                        print 'do this later; need to get specs';
                    end
                fetch next from rule_cur
                into @q1, @q2, @AggType;
            end

            close rule_cur;
            deallocate rule_cur;

            -- if passed, award achievement to all team members
            if @Pass = 1
            begin
                declare reg_cursor cursor for
                select max(p.Email) from Participants p
                inner join Registrations reg on reg.ParticipantID = p.ParticipantID
                where reg.TeamID = @TeamID;

                open reg_cursor;

                fetch next from reg_cursor
                into @Email;

                while @@FETCH_STATUS = 0
                begin
                    exec ProcessAchievement @AchievementID, @Email, 0;

                    fetch next from reg_cursor
                    into @Email;
                end

                close reg_cursor;
                deallocate reg_cursor;

                -- award achievement to team
                exec ProcessTeamAchievement @AchievementID, @TeamID;
            end

            fetch next from ach_cur
            into @AchievementID;        
        end

    close ach_cur;
    deallocate ach_cur;

    fetch next from team_cur
    into @TeamID, @TotalReg, @TotalMin;
end

close team_cur;
deallocate team_cur;

Is there a set-based alternative to what I'm doing here? I need to add that this is running against a small set of records, so performance isn't my concern; best practices for future gargantuan updates are.

Answer 1

To make this set-based you need to fix the procs you are calling so that they either have a table-valued parameter or the code in the proc joins to a table where you have the records you want to process. In the second case you would either mark them as processsed or delete them when you are finished.