如何在存储过程中“迭代”SQL结果但是避免使用游标？

I am fairly new to doing more involved SQL work.

我更擅长做更多涉及SQL的工作。

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.

我的目标是使用查询结果通过电子邮件来推动电子邮件创建。我打算在SQL Server 2008上创建一个存储过程并每周安排两次(最多20封电子邮件,这不会是繁重的电子邮件加载)。

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.

我的理解是,如果我使用游标,我可以为每个条目做一些简单的操作,但是,我发现所有文档和Stack Overflow结果强烈建议这不是一个好策略。

What is the best way to do something like this?

做这样的事情的最佳方法是什么?

4 个解决方案

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

是否直接从您的数据库服务器发送电子邮件是个好主意是另一个问题......

#2

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.

然后,使用while循环,迭代该临时表并获取执行SP所需的值,按行号进行匹配。

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

我们在这里没有做任何繁重的工作,所以在这种情况下我不一定会反对光标。除了它已经这么久了,我不得不刷新如何编码一个。 :)

#3

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调用该函数。

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)

#4

You can do this:

你可以这样做:

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

创建一个SQL代理作业:这将每周两次调用您的存储过程(根据您的需要)

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

您可以在SSMS的所有底部的SQL Server上看到此选项并配置设置

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

您可以通过存储过程控制发送给他们的内容或发送给谁的内容。

#1