I want to highlight the rows having Invalid status using SQL HTML

_{Thursday, February 9, 2017 4:15 PM}

Hi All,

I have one requirement, where I want to highlight the rows having Invalid status.

I want to have <g class="gr_ gr_89 gr-alert gr_spell gr_run_anim gr_inline_cards ContextualSpelling ins-del multiReplace" data-gr-id="89" id="89">sql</g> query using <g class="gr_ gr_132 gr-alert gr_gramm gr_run_anim gr_inline_cards Style multiReplace" data-gr-id="132" id="132">HTML ,</g> below are the sample result

Thankx & regards, Vipin jha MCP

_{Thursday, February 9, 2017 4:23 PM}

this is normally done on the client in the client application not within SQL Server itself.

By chance are you referring to SSRS?

Refer to this for how to do it.

http://stackoverflow.com/questions/23400861/ssrs-dynamically-change-the-cell-background-and-font-color

_{Thursday, February 9, 2017 4:24 PM}

 I have to send this information to end user via  SQL mail HTML format  

Thankx & regards, Vipin <g class="gr_ gr_4 gr-alert gr_spell gr_run_anim gr_inline_cards ContextualSpelling ins-del multiReplace" data-gr-id="4" id="4">jha</g> MCP

_{Thursday, February 9, 2017 4:46 PM}

Then use HTML/inline CSS to color the cell.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Thursday, February 9, 2017 5:16 PM}

I want to colour not only the cells , need to colour entire row having Invalid status

Thankx & regards, Vipin jha MCP

_{Thursday, February 9, 2017 5:39 PM}

Then add the coloring on HTML table row level.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Thursday, February 9, 2017 5:43 PM | 1 vote}

DECLARE @tableA_HTML  NVARCHAR(MAX) ; 
DECLARE @HTML  NVARCHAR(MAX) ;

SET @tableA_HTML =
    N'<H1>TableA:</H1>' +
    N'<table border="1">' +
    N'<tr><th>ID</th><th>City</th><th>Status</th>   </tr>' +
    CAST ( (    
              SELECT             
                 CASE WHEN [Status]='Invalid' THEN     'Red'/*<- Change Color here */ ELSE 'White' END AS "@bgColor" ,'',        
                      td= CAST(   ID AS VARCHAR(50))   ,    ''  ,
                      td= CAST(   City AS VARCHAR(50))   ,    ''    ,   
                      td= CAST(   [Status] AS VARCHAR(50))               
              FROM TableA     
              FOR XML PATH('tr' )   

           )  AS NVARCHAR(MAX) 
         ) +
    N'</table>' ;
    
     
     /*
     ID,City,[Status]
     */

SET @HTML = @tableA_HTML   ; -- Line Break
SET @HTML = REPLACE(@HTML,'<th>','<th/>' ); -- For BackGround Color 
 

SELECT @HTML


EXEC msdb.dbo.sp_send_dbmail 
    @recipients='User@myOrg.com', -- mail Ids
    @subject = 'my @subject Line',
    @body = @HTML,
    @body_format = 'HTML' ;



 

_{Thursday, February 9, 2017 6:01 PM}

Or this:

set QUOTED_IDENTIFIER on
go
declare @table table(ID int identity primary key, [City] varchar(20), [status] varchar(20))
insert into @table (City, status) values('Mumbia','Valid')
insert into @table (City, status) values('Delhi','Invalid')
insert into @table (City, status) values('Chicago','Valid')
declare @Body varchar(max)
SET @body =
'<style type="text/css">h4, body {font-family:
Arial, verdana;}
table{font-size:11px; border-collapse:collapse;}
td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
th{background-color:#99CCFF;}</style>' + N'<h4>Report</h4>'
           + N'<table border="1">' + N'<tr>
<th>ID</th>
<th>City</th>
<th>Status</th> 
'
declare @rows varchar(max)=''
select @rows =@rows +case when status='Invalid' then '<tr><th style="background-color:#ff0000;">'+convert(varchar(10),id) +'</th><th style="background-color:#ff0000;">'+City+'</th><th style="background-color:#ff0000;">'+status+'</th></tr>' 
else 
'<tr><th>'+convert(varchar(10),id) +'</th><th>'+City+'</th><th>'+status+'</th></tr>'  
end
from @table 
select @Body=@Body+@rows +'</table></body></html>'

EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Mail',
@body = @Body,
@body_format ='html',
@recipients = 'myemail@mydomain.com',
@subject = 'subject'
 

_{Monday, June 25, 2018 8:00 AM}

a very elegant solution, just what I was looking for

many Thanks, JohnJames

_{Monday, June 25, 2018 10:33 AM}

I am trying to Update HTML scripts so as only the Status column is changed to Red when it is Invalid, not the whole row.  Any guidance would most welcome

_{Tuesday, June 26, 2018 9:25 AM}

This works with the minimal amount od scripting, to Update a single cell in Table

-- add Red Background for Failed Status

SET@xml =REPLACE(@xml,'<td>Failed</td>','<td bgColor="Red">Failed</td>')