Case Statement in SQL Server (examples included)

Looking to apply a Case statement in SQL Server?

If so, I’ll show you 3 different ways to apply case statements:

(1) For a single condition:

CASE WHEN condition1 THEN result1 ELSE result2 END AS new_field_name

(2) For multiple conditions using AND:

CASE WHEN condition1 AND condition2 THEN result1 ELSE result2 END AS new_field_name

(3) For multiple conditions and results:

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     WHEN condition3 THEN result3
     ...
     END AS new_field_name

Next, I’ll review few examples with the steps to apply case statements in SQL Server.

Steps to Apply Case Statements in SQL Server

Step 1: Create a Table in SQL Server

If you haven’t already done so, create a table in SQL Server.

For example, I created a table called ‘People‘ where the database name is TestDB.

Here is the data and fields under the ‘People’ table:

NameAgeMember
Jack28No
Ben67Yes
Jill33No
Maria72Yes
Bill17No

This is how the table would look like in SQL Server:

Table in SQL Server

You may follow this guide for the steps to create a table in SQL Server Management Studio.

Step 2: Define the Rules

Next, define the rules for the case statement.

In our example, let’s define the following rules:

  • When the person’s age is equal or above 60, then the person is eligible for a ‘senior discount’
  • Else, there should be ‘no discount’

Step 3: Apply the Case Statement in SQL Server

Finally, you can use the following template for a single condition:

CASE WHEN condition1 THEN result1 ELSE result2 END AS new_field_name

In our example:

  • condition1: Age >= 60
  • result1: ‘senior discount’
  • result2: ‘no discount’
  • new_field_name: discount

So the complete syntax would look like this:

select
Name,
Age, 
CASE WHEN Age >= 60 THEN 'senior discount' ELSE 'no discount' END AS discount
from TestDB.dbo.People

Run the query in SQL Server, and you’ll get the following result:

Case Statement in SQL Server

Additional Examples of Case Statements

Example 1: Multiple Conditions using AND:

Let’s now review an example with multiple conditions, where the rules are:

  • When the person’s age is equal or above 60, and the person is a member, then the person is eligible for a ‘membership gift’
  • Else, there should be ‘no membership gift’

You can use this template for multiple conditions using AND:

CASE WHEN condition1 AND condition2 THEN result1 ELSE result2 END AS new_field_name

For our example:

  • condition1 AND condition2: Age >= 60 AND Member = ‘Yes’
  • result1: ‘membership gift’
  • result2: ‘no membership gift’
  • new_field_name: gift

So the complete syntax, for our example, would look like this:

select
Name,
Age, 
Member,
CASE WHEN Age >= 60 AND Member = 'Yes' THEN 'membership gift' ELSE 'no membership gift' END AS gift
from TestDB.dbo.People

Run the query, and you’ll get the following result:

Case Statement in SQL Server

Example 2: Multiple Conditions and Results

For the final section of this guide, let’s review an example with multiple condition and results, where the rules are:

  • When the person’s age is equal or above 60, then the person is eligible for a ‘senior discount’
  • When the person’s age is equal or above 18, and less than 60, then there should be ‘no discount’
  • When the person’s age is below 18, then the person is eligible for a ‘junior discount’

You can then use the following template for multiple conditions and results:

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     WHEN condition3 THEN result3
     ...
     END AS new_field_name

And here is the complete syntax for our example:

select
Name,
Age,
CASE WHEN Age >= 60 THEN 'senior discount'
     WHEN Age >= 18 AND Age < 60 THEN 'no discount'
     WHEN Age < 18 THEN 'junior discount'
     END AS discount
from TestDB.dbo.People

Run the query, and you’ll get:

Case Statement in SQL Server