如何“迭代”存储过程中的 SQL 结果但避免使用游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21557872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to "iterate" through a SQL result in stored procedure but avoid using a cursor?
提问by enderland
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.
我的理解是，如果我使用游标，我可以对每个条目进行一些简单的操作，但是，我发现的所有文档和堆栈溢出结果都强烈表明这不是一个好的策略。
What is the best way to do something like this?
做这样的事情的最好方法是什么？
回答by D Stanley
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...
直接从您的数据库服务器发送电子邮件是否是一个好主意是另一个问题...
回答by Manny
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. :)
不过，我们在这里没有做任何繁重的工作，所以在这种情况下，我不一定建议不要使用游标。除了它已经这么长时间了，我不得不刷新一下如何编码一个。:)
回答by Chad
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)
回答by Maverick
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 AGENT JOB：每周两次调用您的存储过程（取决于您的需要）
- You can see this option on SSMS all the bottom of the SQL Server and configure the settings
- 您可以在 SQL Server 的所有底部的 SSMS 上看到此选项并配置设置
You can control what to sent them or whom to send via stored proc.
您可以通过存储过程控制发送内容或发送对象。
