in my case, i don't use the @query parameter; instead i build a varchar max string, and append multiple FOR XML outputs to it.
I use that string for the @body parameter in sp_send_dbmail.
i may or may not use a cursor for individual emails, it depends on the target audience; if it was an internal group, i'd be find with everyone's emails in the @recipients string.
it requires a little more setup as far as the string manipulation for table headers.
here's a lame example, just building a big varchar of two datasets.
Declare @HTMLBody varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +
'<td align=center><b>Product</b></td>' +
'<td align=center><b>Provider</b></td>' +
'<td align=center><b>Data Source</b></td>' +
'<td align=center><b>Is Linked?</b></td></tr>';
--first result set.
Select @HTMLBody = @TableHead + (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements) + '</table>'
--second result set.
Select @HTMLBody = @HTMLBody + '<table><tr><th>SchemaName</th><th>TableName</th><th>Created Date</th><th>ModifiedDate</th></tr>'
Select @HTMLBody = @HTMLBody + ISNULL((Select Row_Number() Over(Order By name) % 2 As [TRRow],
SCHEMA_NAME(schema_id) As
,
name As
,
create_date As
,
modify_date As
From sys.objects
WHERE create_date > DATEADD(dd,-7,getdate())
OR modify_date > DATEADD(dd,-7,getdate())
Order By modify_date,name
For XML raw('tr'), Elements),'<tr><td colspan="4">No New Or Modified Objects</td></tr>') + '</table>'
-- Replace the entity codes and row numbers
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select @HTMLBody = @HTMLBody + @TableTail
-- return output
Select @HTMLBody
Lowell