iterate email recipients - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

iterate email recipients

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-13 : 06:41:44

Hi

I send out a email from sql server 2005 using....


EXECUTE msdb.dbo.sp_send_dbmail 
     @profile_name = 'Weekly Notification', 
     @recipients = 'xx@xx.xx', 
     @body = @MailBody,
	   @subject = Latest Update',
	   @body_format = 'HTML';

I would like to send this email to a bunch of recipients based on a query, how would that iteration look like?

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-13 : 14:34:29

I currently try this approach

BEGIN

DECLARE @CurrentEmail nVarChar(50)

SELECT @CurrentEmail = CustomerEmail FROM dbo.UserLogin

-- Call procedure here that execute email send
--PRINT @CurrentEmail

END

But when I run this I only get one email adress. I want to execute the send_dbmail once for each recepient. Wht am I doing wrong?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-13 : 14:37:03

Are you sending the same email to multiple recipients? In that case, don't send multiple emails, but instead construct a list of email addresses and pass that as the @recipients parameter.

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-13 : 14:53:47

Yes, I want to send the same email, but will the each recepient see the other email adresses? I don't want that...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-13 : 17:41:20

Use @blind_copy_recipients instead of @recipients. You can construct the list like so:

DECLARE @blind nvarchar(max);
SET @blind=STUFF((
SELECT ';'+CustomerEmail FROM dbo.UserLogin 
FOR XML PATH('')
),1,1,N'')

Then send email to all of them with:

EXEC sp_send_dbmail @blind_copy_recipients=@blind, @subject='Subject', @body='blah blah blah'

You can modify the query to limit rows, etc., just keep the FOR XML PATH('') portion and enclose the whole query inside the STUFF() function. This will remove the leading semicolon from the generated list.

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-14 : 01:35:57

quote:
Originally posted by robvolk

Use @blind_copy_recipients instead of @recipients. You can construct the list like so:
DECLARE @blind nvarchar(max);
SET @blind=STUFF((
SELECT ';'+CustomerEmail FROM dbo.UserLogin
FOR XML PATH('')
),1,1,N'')
Then send email to all of them with:
EXEC sp_send_dbmail @blind_copy_recipients=@blind, @subject='Subject', @body='blah blah blah'
You can modify the query to limit rows, etc., just keep the FOR XML PATH('') portion and enclose the whole query inside the STUFF() function. This will remove the leading semicolon from the generated list.

Excellent, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources