Is this a good script for the MSSQL CASE statement?

Question

I am trying to upload some work from a CF server to SQL Server (2008).

I run a query, and the return statusID value matches one of four colors (green, yellow, orange, and red).

select id, statusID 
from table

If this is the ideal situation to use the case statement, is that right?

select id,  
    case  
        when statusid in (1,20,24)  
            then 'red'
    END as xxxx) as yyyy, *
from TABLE

And if that's right, what happens in xxxx and yyyy above?

Answer 1

You are close to the syntax, although you only need the maximum ASto give the column a name, so you could have something like this (of course, I came up with values ​​to illustrate the option):

SELECT      id,  
            CASE 
                WHEN statusid IN (1,20,24) THEN 'red'
                WHEN statusid IN (2,30,34) THEN 'yellow'
                WHEN statusid 8 THEN 'orange'
                WHEN statusid > 35 THEN 'green'
                ELSE 'unrecognised'
            END AS ColorName,
            statusid

FROM        dbo.table