Mhlewis (5/27/2011)
We've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail.
This is a bit rough... without tables, I just wrote the basic gist here.
But, this will generate a huge dynamic sql statement that will send all the emails at once.
DECLARE @SQL nvarchar(max)
SELECT
@SQL = COALESCE(@SQL + N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''',N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''')
FROM
joblisttable
EXEC sp_executesql @SQL