CODEWITHSUNDEEP
htmlsqlsql-server
alcor8
alcor8

Reputation: 373

Formatting SQL Server Query for color coded table in HTML output

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.

Upvotes: 0

Views: 1067

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

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>

Upvotes: 3

Related Questions