populating @recipients value using msdb.dbo.sp_send_dbmail from a database table

I'm using the stored procedure msdb.dbo.sp_send_dbmail in sql server 2005 to send out emails.

I need to be able to send emails to email addresses stored in a table called emailadd. The msdb.dbo.sp_send_dbmail stored procedure uses the @recipients parameter to guage who the email is sent to, for example:

@recipients = 'me@me.com; myfriend@friend.com,

rather than hard coding these values i want the values to be derived from the emailadd table so that if the email addresses change in the table, the email will go to the right people.

And just so I might also get a nice little "thats cool" instead of "it was late" from AaronAbend, or even "I learnt something new", what is really cool is being able to loop through each distinct company

while (@@fetch_status=0)
begin
set @allrecipients = @allrecipients+@onerecipient+';' --- i do not think a trailing semicolon will be a problem - if it is you need to eliminate with logic
fetch next from getemails into @onerecipient;
end
close getemails;
deallocate getemails;

-- execute the send email command here with @allrecipients...
end

-- this is one approach (sorry - it is past midnight in my timezone and i do not have time to test right now)
-- another would be to execute the emailing process within the cursor loop.
-- if you create an exception block make certain that the cursor is closed and deallocated regardless of the logic path

Enjoy these types of problems, not sure why I haven't seen it before actually...

Can use the e-mail addresses from the table, and can either string them together, or, send individual e-mails. But, identifying which email goes to what email address might be your challenge. Or, are you saying that all emails via this procedure should always go to the recipients found in the email address table...

If you could give some thought as to how to identify an "appropriate" recipient, and / or, how you envisage seeing that happen - I did see your comment that every email would go to everyone in the table, but that is also a small deviation from your original question...

basically the approach will be to either string together the recipients as a single receipient, or do a "loop" to send individually. Kind of depends on how many recipients there could be... @recipients is varchar(max) so that is about 2 gig of addresses :)

Sorry aronabend - did not see your post - again forgot a quick refresh... The cursor is a good idea - should declare with READ_ONLY and FAST_FORWARD...

And using that as an example, aronabend is building up the address so it is one email to lots of recipients - could also do the individual sp_send_dbmail for each recipient (rather than build a string, do the actual send_dmail) if you want to personalise a bit more...

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

OK, OK, then this is why I think the for XML is probably the coolest - it is more adaptable/flexible because it implicitly becomes a string... and do not know why people are so put off by the "xml" keyword...

And just so I might also get a nice little "thats cool" instead of "it was late" from AaronAbend, or even "I learnt something new", what is really cool is being able to loop through each distinct company and provide a single email to all the recipients that belong to that company - all in ONE "select" statement and ONE "exec", no cursors, no loops... while it does use some elements of "classic", don't think you can do that without using the XML version - is it cool yet ?

I always appreciate points - and I think I gave a good example of how to use a cursor (which might help someone in a situation that is more complex than the stated problem) but nobody looking at this thread and conclude that my solution was a good solution to this problem. Cursors are quite slow in TSQL and not a good idea when other approaches are possible.

@aaronabend: apart from not getting a "gosh mark, I have learned something new" or suitably gooey accolade as was afforded zberteoc (see ID:22753412), I am pleased to be sharing the points with you in this solution - it is a legitimate approach, it works, and ever so humble, you have joined in the conversation and definitely assisted me (at least) to arrive at the "accepted" solution. In fact depending on the response to personalised / individualised e-mails, then cursors might be the easiest way to solve. So, what are you saying ?

Hi Mark and Aaron,
Wondered if you could help with a similar question.

I'm have been able to send a View that display's multiple records in a nice table based on certain criteria. I use sp_send_dbmail and MSSQL 05

What I can't figure out, is how to send to the email address associated with those records, not a static one, then loop through and send the next result set to the next recipient.

In the code below, i've declared a static @recipients...but i want it to be pulled from the joined table, and i want it to loop to the next record set. So in the joined tables, 1 email will be associated with, let's say, 10 records...etc.

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.