Navigate
X

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
Using the comparison, you can even output different columns as the result. I.e.
  • 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!

0 0 votes
Article Rating
Ashley:

Recent Posts