How to "iterate" through a SQL result in stored procedure but avoid using a cursor?

Question

I am fairly new to doing more involved SQL work.

My goal is to email using the results of a query to drive email creation. I intend to create a stored procedure and schedule this twice a week (there will be at most 20 emails, this will not be heavy email load) on SQL Server 2008.

SELECT ProjectCodes.ProjectCode, COUNT(Projects.ProjectsID),  ProjectApprovers.EmailApprover
FROM Projects 
INNER JOIN ProjectCodes
    ON Projects.ProjectCodesID=ProjectCodes.ProjectCodesID
INNER JOIN ProjectApprovers
    ON Projects.ProjectCodesID=ProjectApprovers.ProjectCodesID
WHERE ProjectApprovers.IsPrimaryApprover=1
group by ProjectCodes.ProjectCode, ProjectApprovers.EmailApprover

This returns something similar to:

+-------------+-------+--------------+
| ProjectCode | Count | EmailAddress |
+-------------+-------+--------------+
| Code1       |     4 | Email1       |
| Code2       |     2 | Email2       |
| Code3       |     2 | Email3       |
| Code4       |     3 | Email4       |
+-------------+-------+--------------+

What I would like to do is basically loop through this result, running the following:

EXEC msdb.dbo.sp_send_dbmail
@recipients= 'email1',     --email address from above query
@subject='Test email',
@body='You have X projects waiting'    -- where X is the COUNT() term

for each of the rows.

My understanding is I could do this somewhat straightforward for each entry if I use a cursor, but, all the documentation and Stack Overflow results I've found strongly suggest this is not a good strategy.

What is the best way to do something like this?

Answer 1

Usually the reason cursors are discouraged is because there's ways to do what you want without using a cursor. Many developers start in procedural languages so loops are common and natural. Thinking in terms of set-based operations is not "natural" and so cursors are used to mimic loops.

In this case, using a cursor is appropriate because there's no set-based way to send individual emails.

Whether or not it's a good idea to send emails directly from you database server is another question...