When I send emails from stored procedures, I use Database Mail (msdb.dbo.sp_send_dbmail). And to format the body text with clean HTML I used to use cursors (yikes, I got rid of that very quickly), and temporary tables or table variables with WHILE - loops if I needed a result set to be nicely formatted in a table.
This is, however, a lot of work and for every single email a lot of code is needed.
But, on SQL Zaterdag, André pointed out a way to concatenate data from a result set with XML (see for example Anith Sen's blog about this). And that trick can also be used to make clean formatted HTML table.
Romiko wrote a very precise blog about this, so I won't repeat him here: Romiko's blog.
posted @ Wednesday, March 10, 2010 10:46 PM