Victor Nares
asked on
include a row count in SQL generated HTML formatted email
Thank you all in advance for taking a look!
The sql below sends an email update in the form of a table. Each row in the table provides detail for a particular transaction. I would like to include a count of the rows in the body.
Any suggestions would be much appreciated!
The sql below sends an email update in the form of a table. Each row in the table provides detail for a particular transaction. I would like to include a count of the rows in the body.
Any suggestions would be much appreciated!
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @email NVARchar(4000)
SET @xml = CAST(( SELECT email AS 'td','', Created as 'td','', LOC AS 'td'
FROM
stbl_Provider_test_6_25
Where Created > ' 2018-4-1'
and email = 'joe@aol.com'
order by created desc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>
Email Update
</H3>
<table border = 1>
<tr>
<th> email </th> <th> Created </th> <th> Location </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ACE DB Admin', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'joe@aol.com', -- replace with your email address
@subject = 'E-mail in Tabular Format';
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Open in new window