SQL Execute per each result row

Question

I've been googling around for a bit but I can't seem to find an answer to this. I'm trying to use the Database Mail in SQL Server 2005 in a stored procedure where the idea is to send separate mail for each row in a query, each mail depending on an address residing in the rows.

Imagine for example 15 - 50 rows of product orders with the following data: ID, CustomerName, CustomerEmail, ProductName, ProductCategory. Each of the rows contains the customer email as well as the product category. I need to send a confirmation email to each of those customers using their personal data specific to their own rows, as well as the actual order mail to different company people depending on the product category.

How can I send Database Mail dynamically like this?

Thanks in advance!

Edit 1: Datasets etc are not an option, this has to be run exclusively on SQL Server 2005. I suppose I'll have to look into cursors then.

Answer 1

Get the Table into the memory using a DataSet and than run foreach DataRow , get the mail and send it...

Answer 2

Do you mean each time a new row is added (e.g. order is placed)? If so, you can use a Trigger to send the email each time a row is inserted or updated.

Answer 3

No, is not possible. Sending database mail is an invocation of a stored procedure and this is not allowed in queries, nor in functions.

You must send each mail one by one. You can use a cursor on the server in a stored procedure or iterate the table in the client.