CASE expressions are used to evaluate a condition or conditions and return a value. I’ve most often seen this in SELECT statements but that’s not a limit. In fact, documentation states that CASE can be used in any statement or clause that allows a valid expression. I used it differently than SELECT this week in a way we’ll look at below.
Weather for the Week
Let’s create a small set of test data to work with:
CREATE TABLE Temperatures
(
T_Day varchar(20),
T_Temp smallint
);
INSERT INTO Temperatures
VALUES ('Monday',30),('Tuesday',40),('Wednesday',60),('Thursday',70),('Friday',70);
Taking a look at our temperatures for the work week, we have this forecast of temperatures:
SELECT * FROM Temperatures;
GO
What if we don’t want to look at the numbers? Numbers can be hard. We can use CASE to make it simple and just return Cold, Chilly, Warm, or Hot:
SELECT T_Day
, FeelsLike = CASE
WHEN T_Temp < 50 THEN 'COLD'
WHEN T_Temp >= 50 AND T_Temp < 60 THEN 'CHILLY'
WHEN T_Temp >= 60 AND T_Temp < 75 THEN 'WARM'
ELSE 'HOT'
END
FROM Temperatures;
GO
Updating with CASE
That all looks great but what if we find out that something went wrong with our weather model? Any temperatures less than 70 are off by 5 degrees and temperatures 70 or above are off by 10 degrees. Can we use CASE to update our data? You bet we can. We can run this script to get our data updated:
UPDATE Temperatures
SET T_Temp = CASE
WHEN T_Temp < 70 THEN T_Temp + 5
ELSE T_Temp + 10
END;
GO
If we check our Temperatures table again we can confirm that our temperatures have been adjusted appropriately:
SELECT * FROM Temperatures;
GO
Running our first SELECT with a case expression from above, we see the end of the week is actually going to be HOT:
Looks like a beautiful end to the week.
CASE for Divide by Zero
CASE expressions are also useful when it comes to handling divide by zero errors. I touched on this and a few other options for Divide by Zero errors on this blog post a few weeks ago if you want to check it out.
Thanks for reading!
Chad Callihan 