SQL Server HTML Output [Resolved]

Posted by Murugavelmsc under Sql Server on 9/23/2013 | Points: 10 | Views : 1581 | Status : [Member] | Replies : 3
Hi Experts,

I need to send a email via sql server SP.
I have formatted in HTML.
But I need to format based on condition
ie if a employee less than 10% <td bgcolor="green">data</tr>
else
<td bgcolor="red"> data </tr>

declare @msg varchar(max)

select @msg = isnull(@msg,'<table>
<tr><th>Employee No</th><th>Total</th><th>Percentage (%)</th></tr>') +
'<tr>'+
'<td>'+ cast(eno as varchar) + '</td>' +
'<td>'+ cast(convert(decimal(5,2),total) as varchar) + '</td>' +
'<td>'+ cast(convert(decimal(5,2),overdue) as varchar) + '</td>' +
'</tr>
' from #bdtable

Please help me

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Bandi on: 9/23/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Look into the following code.... and tweet your code
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>maker</th>' +
N'<th>Count</th></tr>' +
CAST ( ( SELECT
case when Count>3 then '#F78181' else '#EFFBF2' end AS [@bgcolor],
td = maker, '',
td = Count, ''
FROM (select distinct maker,count(*)as Count from Product
group by maker
) t
ORDER BY maker ASC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name='test',
@recipients='test.com',
@from_address = 'test.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;


Reference:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cddc8466-286f-4dc8-ae08-ecd0b5eb6391/changing-the-font-color-of-a-row-dynamically-using-spsenddbmail

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
tweeted code for coloring tabular data based on condition
declare @tableHTML nvarchar(max) = ''

SET @tableHTML =
N'<table border="1">' +
N'<tr><th>EmployeeNo</th><th>Percentage</th>' +
N'<th>Total</th></tr>' +
CAST ( ( SELECT
case when total<10 then 'GREEN' else '#EFFBF2' end AS [@bgcolor],
td = EmployeeNo, '',
td = Total, '',
td = Percentage, ''
FROM (select eno AS EmployeeNo
,convert(decimal(5,2),total) Total
,convert(decimal(5,2),overdue) Percentage
from employees) t
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
declare @tableHTML nvarchar(max) = ''

SET @tableHTML =
N'<table border="1">' +
N'<tr><th>EmployeeNo</th><th>Percentage</th>' +
N'<th>Total</th></tr>' +
CAST ( ( SELECT
case when total<10 then 'GREEN' else '#EFFBF2' end AS [@bgcolor], td = EmployeeNo, '',
td = Total, '',
td = Percentage, ''
FROM (select eno AS EmployeeNo
,convert(decimal(5,2),total) Total
,convert(decimal(5,2),overdue) Percentage
from employees) t
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML

In the highlighted part you should change the color names.....
The above code is working for me...

Click on "MARK IT AS ANSWER " link

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response