1

I have stored procedure usp_emailRecipients that returns a semicolon-delimited list of email addresses. This is the list of email addresses that will receive an email sent using msdb.dbo.sp_send_dbmail.

How can I set the value of @recipients to the list returned in my stored procedure?

I cannot modify the stored procedure at the moment.

Something like:

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'

Thanks.

fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110

2 Answers2

1

The way you have this coded it will send a string literal as the value for recipients. This is not how this works. The way to do this is to use an OUTPUT variable in usp_emailRecipients. This will require a change in your procedure so you are using an OUTPUT parameter. Then you populate the variable like this.

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

You would do this prior to calling 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'

--EDIT--

Since you say you can not modify your stored procedure you are going to have to do something like use FOR XML to create a delimited list from the result set of your procedure.

This code is 100% untested because we have nothing to work with but it should be pretty close.

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, '')

Then you can send the email like I posted above.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
1

Do you have the flexibility to turn the usp_emailRecipients procedure into a scalar function, lets say udf_emailRecipients? If so, you can then do the following:

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'
bsivel
  • 2,821
  • 5
  • 25
  • 32