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

Question

我对SQL的工作还比较新。

我的目标是使用查询结果来驱动电子邮件的创建。我计划创建一个存储过程，并在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

这将返回类似于以下内容：

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

我希望做的基本上是循环遍历这个结果，运行以下操作：

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

对于每一行数据，我希望你能帮我翻译一下。我的理解是如果使用游标，我可以相对简单地针对每个条目进行操作，但是所有的文档和 Stack Overflow 结果都强烈建议不要采用这种策略。

那么，最好的方法是什么呢？

- enderland

Answer 1

通常不建议使用游标的原因是因为“有其他方法可以在不使用游标的情况下实现你想要的效果”。许多开发人员从过程化语言入手，循环是常见和自然的。以集合为基础进行思考并不是“自然”的，因此使用游标来模拟循环。

在这种情况下，使用游标是适当的，因为没有基于集合的方法来发送单个电子邮件。

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

- D Stanley

---

这是使用游标完全适当的情况之一。将查询输出转储到临时表中，然后对其进行迭代。 - Anon

什么是“基于集合的操作”？ - cja

Answer 2

假设这个查询结果将被存放在一个临时表或表变量中，你可以给这个结果集添加行号，像这样：

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

然后，使用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

我们这里不需要进行任何重性能的操作，所以在这种情况下我不会反对使用游标。只是因为已经有一段时间没有用过了，我需要回忆一下如何编写它。 :)

- Manny

---

另一个选择是创建一个包含查询中所有信息的表，并基于简单的Case语句执行发送电子邮件存储过程。 - Maverick

Answer 3

您可以定义一个函数，使用您想要的参数发送邮件。然后，在查询中执行选择操作，其中选择调用该函数。

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)

- Chad

---

你也可以在一个查询中完成所有操作，但我更喜欢这种方式，因为我觉得它更易读...我相信其他人可能会有不同的看法。 - Chad

Answer 4

你可以这样做：

创建一个 SQL AGENT JOB：每周调用你的存储过程两次（根据你的需求）。

• 你可以在 SSMS 的 SQL Server 底部看到此选项并配置设置。

通过存储过程控制发送什么或向谁发送。

- Maverick

---

他的问题是关于如何在结果集中的每一行执行一个存储过程，而不是如何调度这样的作业。 - Manny