SQL Server query to HTML table email - justify text

Question

We use the following code to send out emails with query results.

DECLARE @tableHTML  NVARCHAR(MAX) ;      
SET @tableHTML =       
N'<H3 style="font-family:verdana;color:darkblue;">Header1</H3>' +   	
N'<H5 style="font-family:verdana;color:darkblue;">SubHeader:</H5>' +   	
N'<H6 style="font-family:verdana;color:darkblue;">SubHeader2<br> 	
SubHeader3</H6>' +   	     
N'<table border="1";>' +       
N'<tr><th>Column1</th><th>Column2</th>' +       
N'<th>Column3</th><th>Column4</th><th>Column5</th>' +       
CAST ( ( SELECT td = SE.MiscColumn1, '',                       
				td = SE.MiscColumn2, '',
                td = SE.MiscColumn3, '',
                td = SE.MiscColumn4, '',
                td = SE.MiscColumn5, ''
        FROM database.dbo.table SE               
		FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX) ) +       N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'Mail_Profile',  
@recipients='******@email.com',   
@copy_recipients = '', 
@subject = 'Sending an email',   
@body = @tableHTML,   
@body_format = 'HTML' ; 

This works very well. The issue is formatting, MiscColumn 2-5 are numbers, and I have asked to make them right-justified. Whenever I try to use HTML tag <TD> inside the SELECT, I get errors.

Where/How can I set justification on certain columns?

Answer 1

Try this:

DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML =
N'<H3 style="font-family:verdana;color:darkblue;">Header1</H3>' +
N'<H5 style="font-family:verdana;color:darkblue;">SubHeader:</H5>' +
N'<H6 style="font-family:verdana;color:darkblue;">SubHeader2<br> SubHeader3</H6>' +
N'<table border="1";>' +
N'<tr>' +
N'<th style="text-align:right;">MiscColumn1</th>' +
N'<th style="text-align:right;">MiscColumn2</th>' + 
N'<th style="text-align:right;">MiscColumn3</th>' + 
N'<th style="text-align:right;">MiscColumn4</th>' +
N'<th style="text-align:right;">MiscColumn5</th>' +          

CAST ( 
	(SELECT
		td = SE.MiscColumn1, '',                        				
		td = SE.MiscColumn2, '',                 
		td = SE.MiscColumn3, '',                 
		td = SE.MiscColumn4, '',                 
		td = SE.MiscColumn5, ''         
		FROM database.dbo.table SE                		
		FOR XML PATH('tr'), TYPE         
		) AS NVARCHAR(MAX) 
) +       
N'</table>';  
EXEC msdb.dbo.sp_send_dbmail   
@profile_name = 'Mail_Profile',   
@recipients='******@email.com',    
@copy_recipients = '',  
@subject = 'Sending an email',    
@body = @tableHTML,    
@body_format = 'HTML'; 

Answer 2

Hi @alhowarthWF,

No need to treat (X)HTML as a raw string.

The issue is formatting, MiscColumn 2-5 are numbers, and I have asked to make them right-justified. Whenever I try to use HTML tag <TD> inside the SELECT, I get errors.

Check it out how to generate well-formed (X)HTML email with embedded CSS for styling: https://learn.microsoft.com/en-us/answers/questions/1373498/sql-send-email-via-db-mail-based-on-the-row-count?page=1&orderby=Helpful&comment=answer-1320866#newest-answer-comment

---