将dbo.sp_send_dbmail参数设置为存储过程返回值

Question

我有一个存储过程usp_emailRecipients，它返回一个以分号分隔的电子邮件地址列表。这是将接收使用msdb.dbo.sp_send_dbmail发送的电子邮件的电子邮件地址列表。

如何将@recipients的值设置为存储过程中返回的列表？

我现在无法修改存储过程。

类似于：

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Bob Jones',
@recipients = 'exec usp_emailRecipients',
@subject = 'Test email',
@execute_query_database = 'Sales',
@query = N'select * from sales',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Sales.txt'

谢谢。

Answer 1

您对其进行编码的方式将发送一个字符串文字作为收件人的值。这不是怎么回事。这样做的方法是在usp_emailRecipients中使用OUTPUT变量。这将需要更改过程，以便使用OUTPUT参数。然后像这样填充变量。

declare @recipientList varchar(max)
exec usp_emailRecipients @recipientList OUTPUT

您应该在调用sp_send_dbmail之前执行此操作。

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Bob Jones',
@recipients = @recipientList,
@subject = 'Test email',
@execute_query_database = 'Sales',
@query = N'select * from sales',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Sales.txt'

--编辑--

既然您说您不能修改存储过程，那么您就必须执行一些操作，比如使用FOR XML从过程的结果集中创建一个分隔列表。

这段代码是100%未经测试的，因为我们没有什么可处理的，但它应该非常接近。

create table #EmailList
(
    EmailAddress varchar(255)
)
insert #EmailList
exec usp_emailRecipients
declare @recipientList varchar(max)
select @recipientList = STUFF((select ';' + EmailAddress
from #EmailList
FOR XML PATH('')), 1, 1, '')

然后你可以像我在上面发布的那样发送电子邮件。

Answer 2

您是否可以灵活地将usp_emailRecipients过程转换为标量函数，比如udf_emailRecipientes？如果是这样，您可以执行以下操作：

Declare @recipients as varchar(max)
Select @recipients= udf_emailRecipients();
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Bob Jones',
@recipients,
@subject = 'Test email',
@execute_query_database = 'Sales',
@query = N'select * from sales',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Sales.txt'