CASE WHEN “I need to learn more T-SQL” THEN GoogleTheResult() END

One of my favorite recent discoveries in my SQL Server work recently, is the awesome T-SQL “CASE…WHEN…THEN” statement. I can’t describe how much I love this thing.

It’s pretty simple really:


SELECT
CASE
WHEN fruit = 'apples' THEN 'not oranges'
WHEN fruit = 'oranges' THEN 'not apples'
ELSE 'neither apples nor oranges'
END AS fruit
FROM fruits


As can be expected, for every row where the column is set to “apples”, this will output “not oranges”. For those with the value “oranges”, it will output “not apples”, and of course, any other value (such as “bananas”) would output “neither apples nor oranges”.

To make things more interesting, you can combine the comparison portion (i.e. x = y) with any WHERE based comparisons, such as:

• X IS NULL
• X IN (value1,value2)
• X >= 7
• X =1 AND >=27
• X+Y>27 OR ISNULL(Z,0) = 0

Thanks Google. I really love this statement, it’s allowed for some pretty funky complex T-SQL scripts recently, and made my life alot easier!