Getting SQL Database Mail to Format HTML Table with 2 Columns

Question

I'm using SQL Server 2005 and am sending a Database Mail formatted in HTML. I am getting the results I need, but I'm having difficulty formatting the table the way I want it.

There are two separate SELECT statements being run that populate the table rows. The problem is that I want them to display side by side as they are in relation to each other, but they will only display one on top of the other. I have tried putting them into two tables within a larger table but it will not shift it over. I haven't used HTML in about 10 years so it's probably a problem with that more than the script itself.

Here is the @body section that contains the two select statements:

    N'<table border="1" cellpadding="0" cellspacing="0"><font face="arial">' +
    N'<th>Store Number</th>' +
    N'<td>'+ CAST ( ( SELECT store_num
              FROM store_results 
              WHERE successful = 'N'
                OR successful IS NULL 
              ORDER BY store_num ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )+'</td>'+
    N'</td>' +
    N'<td><table border="1" cellpadding="0" cellspacing="0"><font face="arial">' +
    N'<th>Reason</th>' +
    N'<td>'+CAST ( ( SELECT 
                CASE successful
                WHEN 'N' THEN 'Failed'              
                    ELSE 'Did Not Run'
                END
            FROM store_results where successful = 'N' OR successful is null
            ORDER BY store_num ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )+'</td>'+
    N'</td>' +
    N'</table>

Any chance you could put a small sample of the HTML that is currently being generated into the question? (Showing maybe one row for each table) — Martin Smith, Mar 09 '11 at 20:52
The email being sent is encrypted and I can't extract the end result HTML, but I linked to a screen of the output table in the comments to the answer below. — TK-42-1, Mar 09 '11 at 21:11

score 1 · Accepted Answer · answered Mar 19 '11 at 15:00

I finally figured this out. You have to set up the header TDs first to create the columns. Instead of using just format the to have it look how you want. Like so:

N'<table border="1" cellpadding="0" cellspacing="0"><font face="arial">' +
N'<tr><td>Store Number</td><td>Reason</td></tr>' +
N'<td>'+ CAST ( ( SELECT 
    td = store_num
    td = reason
        from store_results
        ORDER BY store_num ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +  
N'</table>

You can actually format your cells based on the result of the query by sticking in td/@attribute = case... if you wanted to get fancy with it. I ended up putting all my results into a temp table earlier in the sp to keep the html generation code simpler.

Getting SQL Database Mail to Format HTML Table with 2 Columns

1 Answers1