Wednesday, August 21, 2013

Changing the font color of a row dynamically using sp_send_dbmail

declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th><th>Temperature</th>' +
N'</tr>' +
CAST ( ( select case when Temperature>40 then 'red' else 'black' end as "font/@color",
place as "font/td",'',
case when Temperature>40 then 'red' else 'black' end as "font/@color",
Temperature as "font/td"
from test_fontcolor
order by place
for xml path('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name='test',
@recipients='test@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;

1 comment: