T-SQL 101: 95 Choosing options with CASE in T-SQL

You often need to choose from many outcomes, based upon a specific value. You might want to say if the value is 3 then choose Red, but if the value is 4 then choose Blue, and if the value is 5, then choose Green, etc. The way that you apply this type of logic in T-SQL is by using the CASE statement.

There are several ways you can write CASE statements. In the main image above, I have said that if the value is greater than or equal to 25, then say “Large”. If the value was between 20 and 24, then “Medium”. Otherwise (for all other values), choose “Small”.

The output is as follows:

It’s important to understand that the CASE statement checks each condition, before moving to the next condition, and the conditions do not have to be based on the same value. For example, I could say:

CASE WHEN OuterQuantity >= 25 THEN 'Large'
     WHEN Size = '370ml' THEN 'Can'

The CASE statement stops checking once a condition has been satisfied. In the example above, even if the Size is 370ml, that will not be checked if the OuterQuantity was at or above 25.

Alternate Structure

Another format for CASE can be used when a single value is being checked:

CASE Size WHEN '370ml' THEN 'Standard Can'
          WHEN '740ml' THEN 'Large Bottle'
          WHEN '180ml' THEN 'Small Can'
          ELSE 'Unknown'
END

That only works for a simple list of values.

ELSE

You will notice in the example above that an ELSE clause was provided. This says “if none of the conditions above applies, then choose this”.

The ELSE clause is optional. If you do not have an ELSE clause, and none of the conditions apply, the return value will be NULL.

SQL Clauses

The most common use of CASE statements is in SELECT clauses but it’s important to understand that it can be used in many other places in queries. WHERE clauses commonly use CASE, even ORDER BY can use CASE.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-04-19