• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 996
  • Last Modified:

How do I show blank cells in html formatted email sent from sql database mail?

I have the following stored procedure that sends an html email, but if there's no data in one field it throws the next column into the first empty column.  How do I correct that?
SET @tableHTML =
    N'<h1>Upcoming QA Dates</h1>'
  + N'<table border="1">'
  + N'<tr><th>Job Number</th>'
  + N'<th>FU Date</th>'
  + N'<th>PM Name</th></tr>'
  + CAST ( (  SELECT
                  td = p.JobNumber    , ''
                , td = wo.FUDate2_60   ,  ''
                , td = pm.PMName   ,  ''
              FROM (projmgmtsrv.dbo.tbl_QA AS wo
              LEFT JOIN projmgmtsrv.dbo.tbl_Jobs AS p
              ON wo.ProjectID = p.ProjectID)
              LEFT JOIN projmgmtsrv.dbo.tbl_PM AS pm ON p.PM = pm.PMID              
              FOR XML PATH('tr'), TYPE
           ) AS NVARCHAR(MAX)
  + N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='mssjobbook'
, @recipients='email@here.com'
, @subject = 'Message Subject'
, @body = @tableHTML
, @body_format = 'HTML';
Kevin Smith
Kevin Smith
  • 2
1 Solution
Assuming this happens when one of the three fields is NULL in the database, you can try this:

  + CAST ( (  SELECT
             td = isnull(p.JobNumber,'')    , ''
           , td =  isnull(wo.FUDate2_60,'')   ,  ''
           , td =  isnull(pm.PMName,'')   ,  ''

Open in new window

... and if that doesn't work, here is something else to try:

  + CAST ( (  SELECT
             td = isnull(p.JobNumber,'&nbsp;')    , ''
           , td = isnull(wo.FUDate2_60,'&nbsp;')   ,  ''
           , td = isnull(pm.PMName,'&nbsp;')   ,  ''

Open in new window

Kevin SmithAuthor Commented:
those cause it not to email at all...
David ToddSenior DBACommented:

I try to avoid using XML, but isn't the repeated syntax of td = wrong for a select query? Shouldn't those aliases be different?

Just asking ...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now