TSQL | CASE Expressions

“If you are faced with a mountain, you have several options.
You can climb it and cross to the other side.
You can go around it.
You can dig under it.
You can fly over it.
You can blow it up.
You can ignore it and pretend it’s not there.
You can turn around and go back the way you came.
Or you can stay on the mountain and make it your home.”

Vera Nazarian, The Perpetual Calendar of Inspiration

 

CASE Expressions: The TSQL way by Database with Fun

Why?

Simply put, you use Case expression when you have multiple cases that could happen:

• One statement capable of returning different expressions.
• Evaluate an incoming value.
• Allow you to use conditional logic.
• Multiple conditions can be dubbed inside it.

 Let's say you want some extra pocket money this month.

You ask your Dad. If he's in a good mood, you get the money. (Case 1)

But lets be honest. When is he ever in a good mood. (Why are Indian dad's always in a bad mood?)

He screams at you and so, you turn to your Mom (Case 2)

And Moms are the world's best bank. You get the money.

Either way, you have to get the extra pocket money.

Similarly in programming, we have situations that might get different results based on what values are passed.

What?

Case Statement is a set of conditional tests, used to standardize (beautify) data. 

Or perform checks to protect against errors.

Case Statement is broadly divided into 2 types:

Simple Case Statements 

• used for equality tests
• tests one expression against multiple values
• IN = India, USA = United states of America

Searched Case Statements

• comprehensive expression evaluation format
• work with ranges of data
• Age > 18 AND Age < 25

 

 

How? 

Simple Case Expressions

 

Example 1

To save storage space, a lot of values are stored in database as a code, notation, or abbreviation.

When displaying the same data to user/application, it needs to be transformed into a more meaningful form of information.

Example 2

Database can have multiple set of values that could mean the same thing. User or Application is only required to see a final set of values.

In this case, both M and 0 (zero) means Male & both F and 1 means Female.

Since our database have both these values in it, we need to standardize these into a simpler form.

More..

Searched Case Expressions

Notice there's nothing mentioned between the CASE keyword and the WHEN keyword.

This is because the complete condition is mentioned after each WHEN keyword.

 

Example 3

Let's compare these two examples side by side for better clarity.

The same example is written in both forms of CASE statement.

And the 2nd is usually the preferred format because you only have to learn just one format when you are beginning your TSQL journey.

Also, the 2nd format isn't just limited to equality tests.

GOOD LUCK on your journey.