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 SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

knightEknightCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
knightEknightCommented:
... 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

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

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 ...

Regards
  David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.