CODEWITHSUNDEEP
htmlsql-server-2005database-mail
TK-42-1
TK-42-1

Reputation: 41

Getting SQL Database Mail to Format HTML Table with 2 Columns

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>

Upvotes: 2

Views: 7086

Answers (1)

TK-42-1
TK-42-1

Reputation: 41

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.

Upvotes: 1

Related Questions