title: Writing HTML out from a stored procedure and emailing it
body: <p>My boss insists I send him a daily status report "the hacker way" by outputing HTML from a stored procedure and sending the return to his email using Database mailer. My colleague and I both agree that we should be using SSRS for this matter but since we aren't the ones with the money we unfortunately have to get stuck doing it this way. I myself have never seen this done and am having some formatting issues getting my table to format with cells. Can anyone shed some light on how to get this to work?</p>
  
  <pre><code>BEGIN
  DECLARE @tableHTML  NVARCHAR(MAX) ;
  
  SET @tableHTML =
      N'&lt;b&gt;Shots Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':&lt;/b&gt;' +
      N'&lt;table border="1" width="400"&gt;' +
      CAST ( 
                (SELECT 'Practice:' + PracticeName as Status, 'Aprima ID:'+ AprimaSiteID,
                 'Daily Count:' + CAST(Daily AS nvarchar(5)), 'Monthly Count:' + CAST(Monthly AS nvarchar(5))
  FROM [CriticalKeyDatabase].[dbo].[ShotsManagement]
  
                  ORDER BY Status
                FOR XML PATH('tr'), TYPE 
      ) AS NVARCHAR(MAX) ) +
      N'&lt;/table&gt;';
  
      exec msdb.dbo.sp_send_dbmail @profile_name='aProfileName', @recipients='anEmail@Email.com', @body=@tableHTML, @subject='Daily Shots', @importance='High', @body_format = 'HTML'
  END
  
  GO  
  </code></pre>
  
  <p>I am basing this off a previous employee he had who gave him the idea. I don't see any declarations for cells anywhere in his query. I am not the best at SQL either..  </p>
  
  <pre><code>DECLARE @tableHTML  NVARCHAR(MAX) ;
  
  SET @tableHTML =
      N'&lt;b&gt;Message Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':&lt;/b&gt;' +
      N'&lt;table border="1" width="400"&gt;' +
      CAST ( ( SELECT td = Status,       ' ',
                      td = convert(varchar, StatusValue) + '',       ' '
                FROM 
                (SELECT 'Unsent Messages' as Status, Count(*) as StatusValue
    FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
    where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101) and ReadyToSend = 1
  UNION
  SELECT 'Total Messages' as Status, Count(*) as StatusValue
    FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
    where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)) A
  
                  ORDER BY StatusValue
                FOR XML PATH('tr'), TYPE 
      ) AS NVARCHAR(MAX) ) +
      N'&lt;/table&gt;' +
  
      N'&lt;b&gt;Sender Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':&lt;/b&gt;' +
      N'&lt;table border="1" width="400"&gt;' +
      CAST ( ( SELECT td = Status,       ' ',
                      td = convert(varchar, StatusValue) + '',       ' '
                FROM 
                (SELECT 'Sender: ' + Requestor as Status, Count(*) as StatusValue
  FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
  where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)
  group by Requestor) A
  
                  ORDER BY Status
                FOR XML PATH('tr'), TYPE 
      ) AS NVARCHAR(MAX) ) +
      N'&lt;/table&gt;' +
  
      N'&lt;b&gt;Recipient Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':&lt;/b&gt;' +
      N'&lt;table border="1" width="400"&gt;' +
      CAST ( ( SELECT td = Status,       ' ',
                      td = convert(varchar, StatusValue) + '',       ' '
                FROM 
                (SELECT 'Recipient: ' + Recipient as Status, Count(*) as StatusValue
  FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
  where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)
  group by Recipient) A
                                  ORDER BY Status
                FOR XML PATH('tr'), TYPE 
      ) AS NVARCHAR(MAX) ) +
      N'&lt;/table&gt;' +
  
      N'&lt;b&gt;Event Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':&lt;/b&gt;' +
      N'&lt;table border="1" width="400"&gt;' +
      CAST ( ( SELECT td = Status,       ' ',
                      td = convert(varchar, StatusValue) + '',       ' '
                FROM 
                (SELECT 'Event: ' + EventType as Status, Count(*) as StatusValue
  FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
  where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)
  group by EventType) A
  
                  ORDER BY Status
                FOR XML PATH('tr'), TYPE 
      ) AS NVARCHAR(MAX) ) +
      N'&lt;/table&gt;';
  
      exec msdb.dbo.sp_send_dbmail @profile_name='Localhost', @recipients='someemailAddresses@email.com', @body=@tableHTML, @subject='Daily Statistics', @importance='High', @body_format = 'HTML'
  END  
  </code></pre>
  
  <p>The previous employee's reports looked like this.<br>
  <img src="https://i.stack.imgur.com/G9FFp.png" alt="enter image description here">  </p>
  
  <p>But mine are coming out like this.<br>
  <img src="https://i.stack.imgur.com/QYmlv.png" alt="enter image description here"></p>