使用sp_send_dbmail的存储过程将电子邮件发送给从数据库查询的多个收件人 [英] Stored procedure using sp_send_dbmail to send emails to multiple recipients queried from database

问题描述

就像标题中所说的那样，我正在尝试创建一个存储过程，该过程用于将电子邮件发送到数据库中存储的地址，通常包括多个收件人.我已经尝试了一些方法来解决这个问题，但是我想我遇到了麻烦，并且感谢任何人提供的任何信息.我遇到的主要问题是查询数据库中的地址，并以一种可行的方式将其传递给sp_send_dbmail.

地址表非常简单，看起来像这样，但是更大.两列都是varchars:

idNumber   |  email
__________________________
   a123    |  [e-mail]
   a123    |  [e-mail]
   1-100   |  [e-mail]

因此，如果我们要发送ID号为"a123"的电子邮件，则需要同时发送给史蒂夫和卡罗尔的电子邮件.

这是一个超级简单的过程.这不是逐字记录的，因为这一切都在我的工作计算机上，而是更多地显示了我所要追求的目标.

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

解决方案

您可以执行查询并将值分配给变量，如下所示:

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.

Like the title says, I'm trying to make a stored procedure used to send emails to addresses stored in the database, often including multiple recipients. I've tried a few approaches to this, but I think I'm hitting a wall and would appreciate any input anyone has. The main issue I've had is querying the database for the addresses and passing that to sp_send_dbmail in a way that works.

The address table is pretty simple, looks something like this, but much larger. Both columns are varchars:

idNumber   |  email
__________________________
   a123    |  [e-mail]
   a123    |  [e-mail]
   1-100   |  [e-mail]

So if we're sending to ID number "a123", an email needs to go to both Steve and Carol.

Here's a super simple procedure. It's not verbatim since this is all on my work computer, but more of a skeletal gist of what I'm going after.

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;

It throws and error; it doesn't seem to like concatenating the ID parameter into the query. I tried making a separate procedure to query emails, but passing the ID parameter to the procedure didn't seem to work either. Even if I did successfully pass the parameter and get the query to execute successfully, I'd still need to join the two results in a single string and delimit them with semicolons so they'll play nice with sp_send_dbmail. I think?

SQL wizards, how would you approach this? I'm not wildly experienced, so is there something simple and syntactic I'm doing wrong? Is my approach flawed fundamentally?

I appreciate your input. Thank you.

EDIT: Here's Kritner's working solution! Thanks a bunch!

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

解决方案

You can do a query and assign the values to a variable as such:

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

This will set your @myRecipientLIst to a ";" delimited list of the recipients specified your query.

You could also do the same sort of idea with a SP, just throw them into a temp/variable table and stuff into a semi colon separated list.

EDIT:

Finally to send the mail you could do:

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

COMMENT EDIT:

based on your original query, your stuff query should look something like this:

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

The idea behind this is - for every email found in the email table append to @myrecipientList the email found and a semi colon.

这篇关于使用sp_send_dbmail的存储过程将电子邮件发送给从数据库查询的多个收件人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！