How to "iterate" through a SQL result in stored procedure but avoid using a cursor?
sql, sql-server, sql-server-2008, stored-procedures
Solution
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...
Problem
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?