01.
CREATE
TABLE
DBO.SAMPLEDATA (
02.
FORENAME
VARCHAR
(50)
03.
,SURNAME
04.
)
05.
06.
INSERT
INTO
DBO.SAMPLEDATA
07.
SELECT
'JOHNNY'
,
'BELL'
08.
09.
10.
11.
12.
13.
'JOE'
'BLOGGS'
14.
15.
16.
17.
18.
19.
'JOHN'
'SMITH'
20.
21.
22.
23.
24.
25.
1.
FORENAME, SURNAME,
COUNT
(*)
AS
RECORDS, ROW_NUMBER() OVER(
ORDER
BY
DESC
ID
2.
#
TEMP
3.
FROM
4.
GROUP
FORENAME, SURNAME
5.
DECLARE @ID INT
DECLARE @MAX INT
DECLARE @HTML NVARCHAR(4000)
DECLARE @FORENAME VARCHAR(50)
DECLARE @SURNAME VARCHAR(50)
DECLARE @RECORDS NVARCHAR(10)
SET @HTML = '<
body
><
table
tr
td
>Forename</
>Surname</
>Records</
></
>'
SELECT @MAX = MAX(ID) FROM #TEMP
SET @ID=1
WHILE @ID <= @MAX
BEGIN
SELECT @FORENAME = FORENAME FROM #TEMP WHERE @ID = ID
SELECT @SURNAME = SURNAME FROM #TEMP WHERE @ID = ID
SELECT @RECORDS = RECORDS FROM #TEMP WHERE @ID = ID
SET @HTML = @HTML + '
<
>'+@FORENAME+'</
>'+@SURNAME+'</
>'+@RECORDS+'</
SET @ID = @ID + 1
END
SET @HTML = @HTML + '</
>
>JOHN</
>SMITH</
>4</
>JOHNNY</
>BELL</
>3</
>JOE</
>BLOGGS</
EXECUTE Msdb..sp_send_dbmail
@PROFILE_NAME = 'DATA'
,@RECIPIENTS = 'johnny.bell@test-email.co.uk'
,@BODY = @HTML
,@SUBJECT = 'Forenames and Surnames'
6.
,@BODY_FORMAT = 'HTML'
Naomi N edited Revision 6. Comment: Added new tags, See also and last line. Corrected some typos
Ed Price - MSFT edited Revision 5. Comment: Great article! Removed period from title (per guidelines). Added tags.
Is there a reason why the table is defined using VARCHAR(50), but we later use VARCHAR(150) in the HTML?
No Richard. No reason, just inconsistent DDL on my part. I'll go ahead and correct it now.
Congratulations on winning a medal! blogs.technet.com/.../technet-guru-awards-june-2013.aspx
Hi Johnny, Thanks for the detailed Article. I just want to confirm something. You mention that: "any CSS formatting you wish to do to an HTML email in SQL Server will need to be at INLINE level".
I want to specify <td> width, but it is not supported in HTML5 and the recommendation is to use CSS? What do you suggest?
Hi Terence,
Usually because of the variable length of data contained within a column, I like to specify a fixed width with an NCHAR variable within the result set, this way HTML will wrap the element around each variable automatically. I understand this would be favourable at application/front end level, but it seems that SQL Server compatibility with HTML is quite limited.
Where I declare the holding variables I would change the type from VARCHAR and NVARCHAR to NCHAR and CHAR with a suitable length to hold the data.
Hope this helps!
PERFECT! I got you on the first one hey LOL! Thanks for the feedback, I don't know why I didn't think of it, it is actually so simple.