DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td'
FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'chapman.tim@gmail.com',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile'
用户评论