Link to home
Start Free TrialLog in
Avatar of Victor Nares
Victor NaresFlag for United States of America

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!



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';

Open in new window

Avatar of Russell Fox
Russell Fox
Flag of United States of America image

Hi @Victor, you can try using a windowing function, Row_Number(). I'm not sure if this will work with the XML construct, but you can give it a shot:
SET @xml = CAST(
	( SELECT CAST(ROW_NUMBER() OVER(ORDER BY [email]) AS VARCHAR(10)) AS 'td', '', 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)
	)

Open in new window

This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members 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.