Using a HTML table to send database mail
Posted by Jeff Rix on April 27, 2011 under T-SQL | 
Be the First to Comment
1. Enter Records you want to display into a table variable
DECLARE @WALLSTREET TABLE
(STUDENT VARCHAR(60), STUDENT_ID INT, GRADE varchar(20), STATUS VARCHAR(20), COMMENT VARCHAR(500))
INSERT INTO @WALLSTREET
SELECT reg.First_Name + ‘ ‘ + reg.Last_Name as Student,
wsAssignment.Student_ID,
left(grade.GRADE_DESC, 4) as Grade,
cfgAttCode.NAME as Status,
wsComment.COMMENT
FROM
WS_ASSIGNMENT wsAssignment
LEFT OUTER JOIN ATT_WS attWS
ON wsAssignment.ID = attWS.WS_ID
LEFT OUTER JOIN CFG_ATT_CODE cfgAttCode on attWS.ATT_CODE = cfgAttCode.CODE
LEFT OUTER JOIN CSYESSQL1.YES_LIVE.DBO.REG reg on wsAssignment.STUDENT_ID = reg.Student_ID
LEFT OUTER JOIN CFG_GRADE grade on reg.Grade = grade.GRADE
LEFT OUTER JOIN WS_COMMENT wsComment
on wsAssignment.ID = wsComment.ID
WHERE
CAST(wsAssignment.START_DATE AS DATE) = CAST(@START_DATE AS DATE)
AND
(
attWS.ATT_DATE = (SELECT MAX(ATT_DATE) FROM ATT_WS WHERE WS_ID = wsAssignment.ID)
OR
attWS.ATT_DATE IS NULL
)
AND (attWS.ATT_CODE <> ‘ATT’ OR attWS.ATT_CODE IS NULL)
AND wsAssignment.BUILDING = @BUILDING_ID
update @WALLSTREET
set COMMENT = ”
where COMMENT IS NULL
2. Declare a table variable with one column to enter HTML results
DECLARE @tableHTML5 TABLE
(HTMLRECORDS VARCHAR(8000));
3. Insert the column headers into the table. Enclose select statement in single quotes. Include an opening html body table tag.
INSERT INTO @tableHTML5
SELECT ‘<html><body><table border=”1″ width = “100%”>
<TR BGCOLOR=”CornFlowerBlue”>
<td width = 25% align=”center”> STUDENT NAME
</td><td width = 18% align=”center”> STUDENT ID
</td><td width = 15% align=”center”> GRADE
</td><td width = 16% align=”center”> STATUS
</td><td width = 26% align=”center”> COMMENT
</td></tr>’;
4. Insert the results from the first table variable into the html table variable. Enclose select statement in single quotation marks. Use plus sign(+) to concatenate html with sql columns.
INSERT INTO @tableHTML5
SELECT ‘<tr>
<td width = 25% align=”center”>’+ (cast(STUDENT as varchar(60))) +
‘</td><td width = 18% align=”center”>’+ (cast(STUDENT_ID as varchar(10))) +
‘</td><td width = 15% align=”center”>’+ (cast(GRADE as varchar(20))) +
‘</td><td width = 16% align=”center”>’+ (cast(STATUS as varchar(20))) +
‘</td><td width = 26% align=”center”>’+ (cast(COMMENT as varchar(500))) +
‘</td>’
FROM @WALLSTREET
GROUP BY STUDENT, STUDENT_ID, GRADE, STATUS, COMMENT
ORDER BY STUDENT
5. Insert results into another table variable. Use Rank over to assign each row a unique rank.
DECLARE @tableHTML6 TABLE
(HTMLRECORDS VARCHAR(5000), ROWNUM INT)
INSERT INTO @tableHTML6
select HTMLRECORDS, ROW_NUMBER() OVER(ORDER BY HTMLRECORDS) AS ROWNUM from @tableHTML5
6. Set counter to loop through table while the counter is less than the max number of records. Insert the first record before the while loop. After the while loop add closing tags to the results.
DECLARE @HTMLCOUNTER INT = 1
DECLARE @MAXHTMLRECORD INT
SET @MAXHTMLRECORD = (SELECT MAX(ROWNUM) FROM @tableHTML6)
DECLARE @RESULTS NVARCHAR(MAX)
IF @MAXHTMLRECORD IS NOT NULL
BEGIN
SET @RESULTS = (SELECT HTMLRECORDS FROM @tableHTML6 WHERE ROWNUM = @HTMLCOUNTER)
WHILE @HTMLCOUNTER < @MAXHTMLRECORD
BEGIN
SET @HTMLCOUNTER = @HTMLCOUNTER + 1
SET @RESULTS = @RESULTS + (SELECT HTMLRECORDS FROM @tableHTML6 WHERE ROWNUM = @HTMLCOUNTER)
END
SET @RESULTS = @RESULTS + ‘</td></tr></table></body></html>’
END
7. Set parameters equal to the parameters used in the stored procedure or use hard coded values. Set body format to HTML. Profile name is a database mail profile. Configure using the Database Configuration Manager under the Management tab.
EXEC msdb.dbo.SP_SEND_DBMAIL
@profile_name = ‘Rise’,
@recipients = @EMAIL,
@subject = @SUBJECT,
@body = @RESULTS,
@body_format = ‘HTML’
Results should look like a table with your applied formatting
![clip_image002[4]](http://realworldsql.com/wp-content/uploads/2011/04/clip_image0024.jpg "clip_image002[4]")
