How to set headers in HTML email

Question

This is a follow-up question after a comment left on my last question/answer.

I have this code a HTML table but the first column has no name, which means the headings don't line up properly. This is because I'm using this syntax to identify even/odd rows - cast((select ROW_NUMBER() over(order by id) % 2 as 'td',

How can I set the heading for the first column?

set  @tableHTML = '<html><head><style>' +
   'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
   '</style></head><body>' 
   + '<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:tahoma;">' +
   @textTitle + '</div>' 
   + '<div style="margin-left:50px; font-family:tahoma;"><table cellpadding=0 cellspacing=0 border=0>' +
        '<tr bgcolor=#4b6c9e>' 
            + '<td align=center><font face="calibri" color=White><b>Col1</b></font></td>'     
            + '<td align=center><font face="calibri" color=White><b>Col2</b></font></td>'    
            + '<td align=center><font face="calibri" color=White><b>Col3</b></font></td>'  
            + '<td align=center><font face="calibri" color=White><b>Col4</b></font></td>'   
            + '<td align=center><font face="calibri" color=White><b>Col5</b></font></td>'
            + 
        '</tr>' 

select @body =
   cast((select ROW_NUMBER() over(order by id) % 2 as 'td',
           '',
           isnull(col1,'') as 'td',
           '',     
           isnull(col2,'') as 'td',
           '',      
           isnull(col3,'') as 'td',
           '',
           isnull(col4,'') as 'td',
           '',
           isnull(col5,'') as 'td'        
   from @tableUpdate 
   where notificationType = 'NEWDATE'         
   order by clname
   for XML path('tr'), elements) as nvarchar(max))

select @tableHTML = @tableHTML + @body 
        + '</table></div></body></html>'

Did you check [the link](https://stackoverflow.com/a/39487565/5089204) I provided in your previous answer? This avoids any hassel with blanks, invalid names, `NULL` or missing values and allows styling via *inline CSS* — Shnugo, May 08 '18 at 15:40
Hi @Shnugo Yes i did - thanks! Infact I already had your function in my database from some time ago when I looked into HTML emails. I've used a combination of your function and some of the things below to get it working. It's great :) — Syntax Error, May 08 '18 at 15:41
Thx :-D For the next question: You might add some sample rows of your `@tableUpdate` (best as DDL with `INSERT`). This allows to run your code *stand alone* and reproduce your issue. — Shnugo, May 08 '18 at 15:45
@Shnugo Yeah I will try to. It's always hard to post here because it tends to be proprietary data, which i have to obsfucate first. Then the solution has to be unobsufscated! Often complicates things — Syntax Error, May 08 '18 at 15:47

score 1 · Accepted Answer · answered May 08 '18 at 14:46

Your HTML is stronger than mine, but this should work and allow you to insert your styling where appropriate (if still needed).

select @body =
   cast((select ROW_NUMBER() over(order by id) % 2 as 'td',
           '',
           isnull(col1,'') as 'td',
           '',     
           isnull(col2,'') as 'td',
           '',      
           isnull(col3,'') as 'td',
           '',
           isnull(col4,'') as 'td',
           '',
           isnull(col5,'') as 'td'        
   from @tableUpdate 
   where notificationType = 'NEWDATE'         
   order by clname
   for XML path('tr'), elements) as nvarchar(max))

set @tableHTML = '<html>
                <body>
                    <header>
                        <H3>This would be your header... maybey @textTitle information?</H3>
                        <P>Insert some stuff here if you want</P>
                    </header>
                        <table border = 1>
                        <tr>
                            <th>IDCol</th> 
                            <th>Col1</th> 
                            <th>Col2</th> 
                            <th>Col3/th>
                            <th>Col4</th>
                            <th>Col5</th>
                        </tr>'

select @tableHTML = @tableHTML + isnull(@body,'') + '</table></body></html>'

How to set headers in HTML email

1 Answers1