Установить аргументы 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

То, как вы это закодировали, будет отправлять строковый литерал в качестве значения для получателей. Это не так, как это работает. Способ сделать это - использовать переменную OUTPUT в usp_emailRecipients. Это потребует изменения в вашей процедуре, поэтому вы используете параметр 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_emailRecipients? Если это так, вы можете сделать следующее:

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'