Send Database Mail in HTML Table Format from SQL Server

I gave a sharp tug on my rod, jerking the Bulldawg into the edge of my field of vision. It was still about 15 feet from the boat, and the morning sun reflecting off the water left nothing in view but a silloutte of the lure hoovering a few feet below the glassy surface. I paused a moment before giving another hard jerk, and this time when the lure stopped I felt a hard tug on the end of my pole. I quickly looked over to see the muskie that had chomped down on my lure, still holding the soft rubber in its mouth. I yanked hard to drive the hooks into the hard boney mouth, but I ended up coming up empty. The fish spit the lure and swam off. I was disappointed to have missed the opportunity to fight with the big fish, but I was grateful to have even seen him. I wouldn’t have made contact with the fish were it not for my attention to presentation. The muskie hit out of reaction, not because it was hungry. I was able to elicit the reaction strike by using hard jerks, which caused the lure to pop into view of the muskie range and then stop.
It’s a common practice to have the database send out alerts regarding data inconsistency or problems to affected internal users. I have created a stored procedure that will send out these emails in an html format, which is considerably easier to read than the standard database mail.
To demonstrate, I’ll create a simple set of data to email:

CREATE TABLE ##temp (LakeName VARCHAR(50), Acreage DECIMAL(8,2), Rating INT)
INSERT INTO ##temp
        ( LakeName, Acreage, Rating )
VALUES  ( 'Lake Tomahawk',3462,4),
       ('Big Arbor Vitae',1070,3),
       ('Pewaukee',2437,3),
       ('Okauchee',1210,4),
       ('Random Lake',212,1)

Next I’ll query this table using the normal databasemail stored procedure:

EXEC msdb.dbo.sp_send_dbmail
       @recipients = 'dgugg@domain.com',
       @subject = 'My Lake List',
       @body = Lakes,
       @query = 'select top 5 lakename, acreage, rating from ##temp order by rating';

You can see the results look a bit hard to read:
Imagine how much harder to read it would be with more columns and records!

Next I’ll query using my specially built stored procedure:

EXEC DBMaint.dbo.SendDBEmailwithTabularQuery 
   @qSELECT = N'SELECT TOP 5', 
    @fieldlist = N'LakeName|Acreage|Rating', 
    @qFROM = N'FROM ##temp', 
    @qWHERE = '', 
    @qGroupBy = '',
    @qHaving = '', 
    @qOrderBy = N'ORDER BY Rating', 
    @recipients = N'dgugg@domain.com', 
    @subject = N'My Lake List', 
    @Title = N'Lakes', 
    @SendEmailWithNoResults = 1 

Much easier to read!

So here is the stored procedure. It is set up to send no email if there are no results, but that can be toggled when you call it.

CREATE PROCEDURE [dbo].[SendDBEmailwithTabularQuery]
(
   @qSELECT NVARCHAR(100), --The select part of the sql statement, which can include top X
   @fieldlist NVARCHAR(MAX), --Pipe delimited list of fields, which can include aliases
   @qFROM NVARCHAR(MAX), --The from part of the sql statment, which can include joins
   @qWHERE NVARCHAR(MAX) = '', --The where part of the sql statement
   @qGroupBy NVARCHAR(MAX) = '',--The group by clause
   @qHaving NVARCHAR(MAX) = '',--The having clause
   @qOrderBy NVARCHAR(MAX) = '', --The order by part of the sql statement
   @recipients NVARCHAR(4000), --The recipients of the email
   @subject NVARCHAR(400), --The subject of the email
   @Title NVARCHAR(4000) = '', --The title of the html table that holds the query results
   @SendEmailWithNoResults BIT = 0 --Send an email even if no records are generated
)

AS
BEGIN

   --Declare initial variable.
   DECLARE @xml NVARCHAR(MAX)
   DECLARE @body NVARCHAR(MAX)
   DECLARE @sql NVARCHAR(MAX)
   DECLARE @resultexist NVARCHAR(MAX)
   DECLARE @tblfieldheader NVARCHAR(MAX) = ''
   DECLARE @tempfield NVARCHAR(MAX) = ''
   CREATE TABLE #Fields (ID INT IDENTITY(1,1),field NVARCHAR(MAX))
   DECLARE @i INT = 1, @j INT = 0, @SendEmail INT
   DECLARE @splitcnt INT
   DECLARE @fieldcount INT  
  
   --Find the number of fields in the query  
   SELECT @splitcnt = LEN(@fieldlist)-LEN(REPLACE(@fieldlist,'|',''))
   --Loop through the fields and put each on into the #Fields temp table as a new record
   WHILE @j <= @splitcnt
       BEGIN
           INSERT INTO #Fields ( field ) SELECT s FROM DBMaint.dbo.SplitString(@fieldlist,'|') WHERE zeroBasedOccurance = @j
           SET @j += 1
       END
   
   SELECT @fieldcount = @splitcnt + 1 --Will be the splitcnt + 1, otherwise MAX(ID) FROM #Fields  

   --Start setting up the sql statement for the query.
   SET @sql = @qSELECT
   --Loop through the #Fields table to get the field list
   WHILE @i <= @fieldcount
       BEGIN
           SELECT @tempfield = field FROM #Fields WHERE ID = @i
           --------------------------------------------------------------------------------------------------------------------------------------------------------------
           --This next section is required in case a field is aliased.  For the xml, we need to get rid of the aliases, the table header will only require the aliases.
           --NULL values need to be shown as a string = 'NULL' or the html table will just skip the cell and all values after that in the row will be shifted left.
           ---------------------------------------------------------------------------------------------------------------------------------------------------------------
           IF RIGHT(@tempfield,1) = ']' OR CHARINDEX(' as ',@tempfield) = 0
               BEGIN
                   --Set the xml field to be the entire field name
                   SET @sql = @sql + ' ISNULL(CAST(' + @tempfield + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','''','
                   --Set the table header field to be the entire field name
                   SET @tblfieldheader = @tblfieldheader + '<th>' + @tempfield + '</th>'
               END          
           ELSE 
               BEGIN
                   --Set the xml field to be the field name minus the alias
                   SET @sql = @sql + ' ISNULL(CAST(' + LEFT(@tempfield,LEN(@tempfield) - (CHARINDEX(' sa ',REVERSE(@tempfield))+3)) + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','''','
                   --Set the table header field to be the field name's alias
                   SET @tblfieldheader = @tblfieldheader + '<th>' + RIGHT(@tempfield,CHARINDEX(' sa ',REVERSE(@tempfield))-1) + '</th>'
               END
           --Increment the counter.
           SET @i += 1
       END
   --Trim the extra four characters of the end of @sql.      
   SET @sql = LEFT(@sql, LEN(@sql)-4)
   --Add the from, where, group by, having, and order by clause to the select statement.
   SET @sql = @sql + ' ' + @qFROM + ' ' + @qWHERE + ' ' +  @qGroupBy + ' ' + @qHaving + ' ' + @qOrderBy
   --Put the set xml command around the sql statement.
   SET @sql = 'SET @XML = CAST(( ' + @sql + ' FOR XML PATH(''tr''),ELEMENTS ) AS NVARCHAR(MAX))'
   --Run the sql that will create the xmll.
   EXEC sp_executesql @sql, N'@xml nvarchar(max) output', @xml OUTPUT
   
   --Create the body of the email, which contains the xml results of the query.
   SET @body = '<html><body><H3>' + @Title + '</H3><table border = 1><tr>' + @tblfieldheader + '</tr>' + @xml + '</table></body></html>'

   --Drop the fields temp table.
   DROP TABLE #Fields

   --Set the variable that will be tested to verify there was at least one result.
   SET @resultexist = 'IF NOT EXISTS(SELECT TOP 1 1 ' + @qFROM + ' ' + @qWHERE + ' ' + @qGroupBy + ' ' + @qHaving + ') SET @SendEmail = 0 ELSE SET @SendEmail = 1'
   --Capture whether or not any rows were returned
   EXEC sp_executesql @resultexist, N'@SendEmail int output', @SendEmail OUTPUT
   --Check the variable.
   IF @SendEmail = 1
       BEGIN  
           --If rows were returned, send the email.
           EXEC msdb.dbo.sp_send_dbmail
                   @recipients = @recipients,
                   @subject = @subject,
                   @body = @body,
                   @body_format = 'HTML';
       END
   ELSE IF @SendEmailWithNoResults = 1
       BEGIN
           EXEC msdb.dbo.sp_send_dbmail
                   @recipients = @recipients,
                   @subject = @subject,
                   @body = 'Query returned no results';
       END
END


GO

This stored procedure uses a string splitting table valued function. Many versions of this can be found online, but here is the one I use. Please note I am not the original author, but I am unsure of where I found this, so I can’t give credit to the correct persion.

CREATE FUNCTION [dbo].[SplitString] 
    (
        @str NVARCHAR(4000), 
        @separator CHAR(1)
    )
    RETURNS TABLE
    AS
    RETURN (
        WITH tokens(p, a, b) AS (
            SELECT 
                1, 
                1, 
                CHARINDEX(@separator, @str)
            UNION ALL
            SELECT
                p + 1, 
                b + 1, 
                CHARINDEX(@separator, @str, b + 1)
            FROM tokens
            WHERE b > 0
        )
        SELECT
            p-1 zeroBasedOccurance,
            SUBSTRING(
                @str, 
                a, 
                CASE WHEN b > 0 THEN b-a ELSE 4000 END) 
            AS s
        FROM tokens
      )