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?

D Stanley · Accepted Answer · 2015-06-03 20:40:01Z

7

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...

edited Jun 3, 2015 at 20:40

answered Feb 4, 2014 at 16:39

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

2 Comments

Anon Over a year ago

This is one of the times when using a cursor is completely appropriate. Dump the query output into a temp table and iterate over that.

cja Over a year ago

What do you mean by a "set-based operation"?

Manny · Accepted Answer · 2014-02-04 17:01:34Z

3

Assuming this is going into some temp table or table var, you can add a row number to that result set, like so:

SELECT ROW_NUMBER() OVER (ORDER BY ProjectCodes.ProjectCode) RowNumber, ProjectCodes.ProjectCode, COUNT(Projects.ProjectsID),  ProjectApprovers.EmailApprover
...

And then, using a while loop, iterate over that temp table and grab the values you require to execute the SP, matching by row number.

DECLARE @i int = 0
DECLARE @count int = -- length of query results

WHILE (@i < @count)
BEGIN
    SELECT @emailAddress = EmailApprover, ...
    FROM @YourTempResults
    WHERE RowNumber = (@i + 1) -- row number is 1-based

    EXEC msdb.dbo.sp_send_dbmail @recipients = @emailAddress, ...

    SET @i = @i + 1
END

We're not doing any heavy lifting here though, so I wouldn't necessarily advice against a cursor in this case. Except that it's been so long, I'd have to refresh on how to code one. :)

edited Feb 4, 2014 at 17:01

answered Feb 4, 2014 at 16:45

Manny

9761 gold badge6 silver badges17 bronze badges

1 Comment

Maverick Over a year ago

The another option to do is Create a table with all the information from your query and based on simple Case statement you can execute the sent email proc.

Chad · Accepted Answer · 2014-02-05 18:50:30Z

1

You could define a function that did the send mail with the parameters you desire. Then do a select on your query where the select calls the function.

Select SendPMMail(ProjectCode, EmailAddress, ProjectCount) from
(SELECT ProjectCodes.ProjectCode as ProjectCode, 
        COUNT(Projects.ProjectsID) as ProjectCount, 
        ProjectApprovers.EmailApprover as EmailAddress
   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)

answered Feb 5, 2014 at 18:50

Chad

1,5622 gold badges16 silver badges41 bronze badges

1 Comment

Chad Over a year ago

You could also do it all in one query but I prefer this way since I find it more readable... i am sure others disagree.

Maverick · Accepted Answer · 2014-02-04 16:41:22Z

0

You can do this:

Create a SQL AGENT JOB : That will call your stored procedure every 2 time a week ( depending on your need)

You can see this option on SSMS all the bottom of the SQL Server and configure the settings

You can control what to sent them or whom to send via stored proc.

answered Feb 4, 2014 at 16:41

Maverick

1,1851 gold badge8 silver badges16 bronze badges

1 Comment

Manny Over a year ago

His question is regarding how to execute an SP per each row in a result set, not how to schedule such a job.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related