Send email for each row in a result set

Question

I'd like to send an email for each row of a result set using sp_send_dbmail.

What is the appropriate way to accomplish this without using loops?

Edit: I'm not insisting a loop is not proper here, but is there a set based way to do this. I've tried creating a function, but a function cannot call a stored proc inside it. Only another func or extended sp (which I'd rather not do either).

Answer 1

It must be a row-by-row operation if you need an email per row. It's not a standard set based action.

Either you WHILE through it in SQL or you "for each" in a client language

I would not send emails from triggers BTW: your transaction is open while the trigger executes

Answer 2

This case is exactly what loops are good for (and designed for).

Since you do things that fall out of database scope, it's perfectly legitimate to use loops for them.

Databases are designed to store data and perform the queries against these data which return them in most handy way.

Relational databases can return data in form of rowsets.

Cursors (and loops that use them) are designed to keep a stable rowset so that some things with each of its rows can be done.

By "things" here I mean not pure database tricks, but real things that affect the outer world, the things the database is designed for, be it displaying a table on a webpage, generating a financial report or sending an email.

It's bad to use cursors for pure database tasks (like transforming one rowset to another), but it's perfectly nice to use them for the things like that one you described.

Set based methods are designed to work within a single transaction.

If your set-base query will fail for some reason, you database will revert to the state in was before, but you cannot "rollback" a sent email. You won't be able to keep track of your messages in case of an error.

Answer 3

The best way to accomplish this is to put your email sending logic in a user defined function.

Then you would simply call SELECT MyEmailFunc(emailaddress) FROM MyTable

It avoids loops and you can even use it in an update statement to show that the email was sent. For example:

UDPATE MyTable SET SENT = MyEmailFunc(emailaddress) WHERE sent = 0

Answer 4

Set up a data-driven subscription in SQL Server Reporting Services :-D

Sounds like an SSRS requirement to me - TSQL isn't really designed for reporting in and of itself.

Answer 5

Not the best practice but if you want to avoid loops:

You could create a "SendMails" table, with a trigger on Insert

The sp_send_dbmail is called from inside the trigger

then you do:

Truncate Table SendMails

insert into SendMails (From, To, Subject,text) Select field1,field2,field3,field4 from MyTable