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:
WHEN fruit = 'apples' THEN 'not oranges'
WHEN fruit = 'oranges' THEN 'not apples'
ELSE 'neither apples nor oranges'
END AS fruit
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
WHEN columnA is null THEN columnB
WHEN columnA+columnB > 27 THEN columnB/(SELECT MAX(columnA) FROM TABLE)
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!