Formatting SQL Server Query for color coded table in HTML output

Question

I'm coding a SQL Server query that queries hard drive information from a series of computers and highlights the drives with less than 20% capacity. I'd like to make the ones with 10% or less capacity yellow. I'm using a case statement in my query and I'm having trouble with the syntax.

CASE 
   WHEN ((UsedSpace/CAST(TotalSpace as decimal(18,2))) > 0.9) 
      THEN CAST('''<font color="yellow">'' + [FreeSpace] + ''</font>''' AS varchar(150)) 
      ELSE [FreeSpace] 
END AS 'td',

This causes an error:

Conversion failed when converting the varchar value ''' + [FreeSpace] + ''' to data type int.

This like means that the output of the CASE expression is an integer value. You've got a CASE condition that returns CHAR data instead. Make sure each WHEN clause returns Character data — Sparky
– Sparky, Commented Jun 14, 2021 at 16:35

John Cappelletti · Accepted Answer · 2021-06-14 16:35:36Z

3

You're mixing data types... INT and Strings

Assuming you are creating and HTML Table, one option is to use concat() and XML. concat() is an easy way to mix data types without having to be concerned with conversions.

Example

Declare @yourtable table (UsedSpace int,TotalSpace int,FreeSpace int)
Insert into @YourTable values 
 ( 95,100,5)
,( 80,100,20)

Select 

 CASE WHEN ((UsedSpace/CAST(TotalSpace as decimal(18,2))) > 0.9) 
      THEN convert(XML,concat('<font color="yellow">',[FreeSpace],'</font>'))
      ELSE concat('',FreeSpace) END as 'td'
 From @YourTable
 For XML Path('tr')

Results

<tr>
  <td>
    <font color="yellow">5</font>
  </td>
</tr>
<tr>
  <td>20</td>
</tr>

answered Jun 14, 2021 at 16:35

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Cappelletti Over a year ago

@alcor8 Always happy to help

Collectives™ on Stack Overflow

Formatting SQL Server Query for color coded table in HTML output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related