sql - 使用 sp_send_dbmail 将电子邮件发送给从数据库查询的多个收件人的存储过程

Question

正如标题所说，我正在尝试制作一个存储过程，用于将电子邮件发送到数据库中存储的地址，通常包括多个收件人。我已经尝试了几种方法来解决这个问题，但我认为我正在碰壁，我会很感激任何人提供的任何意见。我遇到的主要问题是查询数据库中的地址并将其以有效的方式传递给 sp_send_dbmail。

地址表非常简单，看起来像这样，但要大得多。两列都是 varchars:

idNumber   |  email
__________________________
   a123    |  steve@test.com
   a123    |  carol@test.com
   1-100   |  mary@test.com

因此，如果我们要发送给 ID 号“a123”，则需要向 Steve 和 Carol 发送一封电子邮件。

这是一个 super 简单的过程。这不是逐字逐句的，因为这一切都在我的工作电脑上，但更多的是我要追求的东西的骨架要点。

CREATE PROCEDURE sendMail
  @idNumber varchar(MAX),
  @subject varchar(MAX),
  @body varchar(MAX)
EXEC msdb.dbo.sp_send_dbmail
  @recipients = "EXEC SELECT email FROM emailTable WHERE idNumber = " + @idNumber + "';",
  @subject = @subject,
  @body = @body;

它抛出错误；它似乎不喜欢将 ID 参数连接到查询中。我尝试制作一个单独的程序来查询电子邮件，但将 ID 参数传递给该程序似乎也不起作用。即使我确实成功传递了参数并使查询成功执行，我仍然需要将两个结果连接到一个字符串中并用分号分隔它们，这样它们才能很好地与 sp_send_dbmail 配合使用。我想？

SQL 向导，您将如何处理这个问题？我没有丰富的经验，所以我做错了什么简单的句法吗？我的方法是否存在根本性缺陷？

非常感谢您的意见。谢谢。

编辑:这是 Kritner 的工作解决方案!非常感谢!

CREATE PROCEDURE testMail2
@idNumber varchar(MAX)
AS
BEGIN
DECLARE @recipientList varchar(MAX)
SET @recipientList = (STUFF((SELECT ';' + email FROM emailTable WHERE idNumber = @idNumber FOR XML PATH(' ')),1,1,''))
EXEC msdb..sp_send_dbmail
@recipients=@recipientList,
@subject='Subject Line',
@body='Body Text'
END

Answer 1

您可以执行查询并将值分配给这样的变量:

DECLARE @myRecipientList varchar(max)
SET @myRecipientList = (STUFF((SELECT ';' + emailaddress FROM table FOR XML PATH('')),1,1,''))

这会将您的@myRecipientLIst 设置为“;”指定您的查询的收件人的分隔列表。

您也可以对 SP 做同样的想法，只需将它们放入临时/变量表中，然后将 stuff 放入分号分隔的列表中即可。

编辑:

最后发送您可以做的邮件:

EXEC msdb.dbo.sp_send_dbmail
  @recipients = @recipientList,
  @subject = @subject,
  @body = @body // ........

评论编辑:

根据您的原始查询，您的资料查询应如下所示:

DECLARE @myRecipientList varchar(max)
SET @myRecipientList = STUFF((SELECT ';' + email FROM emailTable WHERE idNumber = @idNumber FOR XML PATH('')),1,1,'')

这背后的想法是 - 对于在电子邮件表中找到的每封电子邮件，将找到的电子邮件和一个分号附加到@myrecipientList。