SQL Server HTML Output [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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/

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

Latest Posts