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).
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.
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
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
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.
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
Chirs Lively - I tried using this but still get the 'Only functions and extended stored procedures can be executed from within a function' error message.