SQL Execute per each result row

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 depe开发者_如何学Cnding 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.

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.

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

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.