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

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.