SQL Server T-SQL CASE Statement Examples

By: Koen Verbeeck   |   Updated: 2021-04-20   |   Comments (2)   |   Related: More > T-SQL

Problem

In the T-SQL scripting language, you can use the SQL CASE statement to evaluate a condition and return one or more result expressions. This SQL Tutorial will teach you when and how you can use CASE in T-SQL statements.

Solution

The CASE expression is used to build IF … THEN … ELSE statements into your Microsoft SQL Server T-SQL code. CASE is used within a SQL statement, such as SELECT or UPDATE. Don’t mistake CASE for the IF ELSE control of flow construct, which is used to evaluate the conditional execution of SQL statements.

Let’s illustrate with an example. The following SQL statement will return "Monday" if today is a Monday, otherwise it returns "Not a Monday".

SET DATEFIRST 1; -- first day of the week is a Monday
SELECT CASE WHEN DATEPART(WEEKDAY,GETDATE()) = 1
                THEN 'Monday'
                ELSE 'Not a Monday' END;

The following SQL script does the same, but rather uses the IF … ELSE construct to switch between two different statements, instead of calculating the result in one single statement:

SET DATEFIRST 1; -- first day of the week is a Monday
IF(DATEPART(WEEKDAY,GETDATE()) = 1)
BEGIN
    SELECT 'Monday';
END
ELSE
BEGIN
    SELECT 'Not a Monday';
END

For the remainder of the tip, example T-SQL queries are run on the AdventureWorks2017 sample database. At every section, the syntax is given, so you can modify it to suit your queries or you can copy paste the query and change any column or table name.

The Searched CASE Expression

The first format for the CASE expression has the following syntax:

CASE WHEN <condition> THEN <expression if true> ELSE <expression if false> END 

The ELSE argument is optional. The example given in the introduction uses this format. Let’s take a look at some examples using the Employee table in the HumanResources schema.

This query decodes the MaritalStatus column into a more elaborate description:

SELECT
     [BusinessEntityID]
    ,[MaritalStatus]
    ,MaritalStatusDesc = CASE WHEN [MaritalStatus] = 'S' THEN 'Single' ELSE 'Married' END
FROM [AdventureWorks2017].[HumanResources].[Employee];

If we leave out the ELSE clause, NULL values will be returned instead of "Married".

SELECT
     [BusinessEntityID]
    ,[MaritalStatus]
    ,MaritalStatusDesc = CASE WHEN [MaritalStatus] = 'S' THEN 'Single' END
FROM [AdventureWorks2017].[HumanResources].[Employee];

As a best practice, it’s a good idea to always include an ELSE argument. The WHEN condition can also use different data types.

The following example checks on dates:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[BirthDate]
    ,Generation = CASE WHEN [BirthDate] < '1965-01-01' THEN 'Boomer' ELSE 'Not a boomer' END
    ,[MaritalStatus]
    ,[Gender]
    ,[HireDate]
FROM [AdventureWorks2017].[HumanResources].[Employee];

And this query checks on integers:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[VacationHours]
    ,HolidayNeeded = CASE WHEN [VacationHours] > 50 THEN 'Needs a vacation' ELSE 'Can keep working' END
FROM [AdventureWorks2017].[HumanResources].[Employee];

Any data type can be used in the WHEN condition, as long as the result is either true or false. The expressions in the THEN or ELSE argument can also be of other data types than just strings. For example, we can return numbers as well.

In this example, we’ll update the vacation hours of any employee which has less than 20 hours of vacation remaining:

BEGIN TRAN
UPDATE [HumanResources].[Employee]
SET [VacationHours] = CASE WHEN [VacationHours] < 20 THEN 20 ELSE [VacationHours] END;
ROLLBACK

In the above script, ROLLBACK is used to prevent an update from actually making changes to the Employee table. You can find more info in What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?.

The CASE expression can return any data type, but you must be careful of data types being different between the THEN and the ELSE argument. If the data types are not compatible, an error is thrown:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[BirthDate]
    ,[MaritalStatus]
    ,[Gender]
    ,[HireDate]
    ,CASE WHEN [SalariedFlag] = 1 THEN [MaritalStatus] ELSE -1 END
FROM [HumanResources].[Employee];

In the tip SQL Server CASE Expression Overview, Aaron has written a section about data type precedence in the CASE expression.

Nesting CASE Expressions

With the searched CASE expression, we can have multiple WHEN conditions:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[HireDate]
    ,Seniority = CASE   WHEN DATEDIFF(YEAR,[HireDate],GETDATE()) > 10 THEN 'Longer than 10 years'
                        WHEN DATEDIFF(YEAR,[HireDate],GETDATE()) = 10 THEN 'Exactly 10 years'
                        WHEN DATEDIFF(YEAR,[HireDate],GETDATE()) < 10 THEN 'Shorter than 10 years'
                        ELSE 'N/A' END
FROM [HumanResources].[Employee];

It’s also possible to start an entire new CASE expression on a THEN or ELSE argument. For example, this T-SQL statement has a nested CASE expression:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[BirthDate]
    ,CASE WHEN [JobTitle] LIKE '%Marketing%'
            THEN CASE WHEN [BirthDate] >= '1984-01-01'
                        THEN 'Social Media Expert'
                        ELSE 'Only knows Facebook' END
            ELSE 'N/A' END
FROM [HumanResources].[Employee];

Nesting many CASE expressions can make the SQL code harder to read and is more prone to errors, especially if data types are being mixed. Be aware there’s a limit on nesting, as explained in Aaron’s tip about CASE.

The Simple CASE Expression

The searched case statement is probably used the most since it’s very flexible, but there’s another format possible: the simple CASE expression.

CASE <input>
    WHEN <eval_expression_1> THEN <expression if true>
    WHEN <eval_expression_2> THEN <expression if true>
    …
    WHEN <eval_expression_N> THEN <expression if true>
    ELSE <default expression> END 

This pattern is similar to the switch expression typically found in programming languages, or the DECODE function in other database systems. Let’s illustrate with an example:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[BirthDate]
    ,[SalariedFlag]
    ,IsEmployee = CASE [SalariedFlag]
                    WHEN 1 THEN 'Salaried Employee'
                    WHEN 0 THEN 'Contractor'
                    ELSE 'N/A' END
FROM [HumanResources].[Employee];

The downside of the simple CASE statement is that you can only check for equality.

The IIF function

With SQL Server 2012, the IIF function was introduced into the T-SQL language. The syntax is as follows:

IIF(<condition>,<expression if true>,<expression if false>)

It’s a shorthand for a searched CASE. Let’s rewrite the following statement:

SELECT
     [BusinessEntityID]
    ,[MaritalStatus]
    ,MaritalStatusDesc = CASE WHEN [MaritalStatus] = 'S' THEN 'Single' ELSE 'Married' END
FROM [AdventureWorks2017].[HumanResources].[Employee];

Using IIF, becomes the following query:

SELECT
     [BusinessEntityID]
    ,[MaritalStatus]
    ,MaritalStatusDescIIF = IIF([MaritalStatus] = 'S','Single','Married')
FROM [AdventureWorks2017].[HumanResources].[Employee];

Using IIF leads to more compact code. Unlike other languages – like DAX – the false branch of IIF is not optional, it must always be specified.

Using CASE in other types of statements

CASE can not only be used in SELECT statements, but at any other place where an expression can be written. This includes UPDATE, DELETE, SET, MERGE, a WHERE or HAVING clause and an ORDER BY clause.

Let’s illustrate with some examples. Suppose we want to sort our data by Jobtitle, but not by alphabetical order but by a custom order. We can do this using a CASE statement in the ORDER BY:

SELECT
     [BusinessEntityID]
    ,[JobTitle]
FROM [AdventureWorks2017].[HumanResources].[Employee]
ORDER BY CASE   WHEN [JobTitle] LIKE 'Chief%'       THEN 1
                WHEN [JobTitle] LIKE 'Vice%'        THEN 2
                WHEN [JobTitle] LIKE '%Senior%'     THEN 3
                WHEN [JobTitle] LIKE '%Manager%'    THEN 4
                ELSE 5 END
        , [JobTitle]

This query will first sort on job titles starting with "Chief", then on the titles starting with "Vice", followed by any jobtitle containing "Senior" and then by those containing "Manager". The other job titles come last. When there’s a tie, the job titles will be sorted alphabetically because we added JobTitle as a second column in the ORDER BY.

The following statement uses a CASE expression to group people into certain categories. Then we’re counting how many people are in those categories.

SELECT
     Staff      = CASE  WHEN [JobTitle] LIKE '%chief%' OR [JobTitle] LIKE '%vice president%' THEN 'Upper Management'
                        WHEN [JobTitle] LIKE '%manager%'    THEN 'Middle Management'
                        WHEN [JobTitle] LIKE '%senior%'     THEN 'Senior Staff'
                        ELSE 'Staff' END
    ,StaffCount = COUNT(
                    CASE    WHEN [JobTitle] LIKE '%chief%' OR [JobTitle] LIKE '%vice president%' THEN 'Upper Management'
                            WHEN [JobTitle] LIKE '%manager%'    THEN 'Middle Management'
                            WHEN [JobTitle] LIKE '%senior%'     THEN 'Senior Staff'
                            ELSE 'Staff' END
                        )
FROM [AdventureWorks2017].[HumanResources].[Employee]
GROUP BY CASE
        WHEN [JobTitle] LIKE '%chief%' OR [JobTitle] LIKE '%vice president%' THEN 'Upper Management'
        WHEN [JobTitle] LIKE '%manager%'    THEN 'Middle Management'
        WHEN [JobTitle] LIKE '%senior%'     THEN 'Senior Staff'
        ELSE 'Staff' END
ORDER BY [StaffCount];

Since we cannot re-use aliases in T-SQL, we need to use the same expression inside the COUNT aggregate and in the GROUP BY clause. We can however use aliases in the ORDER BY clause, as demonstrated by using the StaffCount alias to sort the data on. If we would like to filter the data on Staff (in the WHERE clause) or on StaffCount (in the HAVING clause), we would need to repeat the same expression again. We might simplify such SQL statements by using a subquery:

SELECT
     Staff
    ,StaffCount = COUNT(Staff)
FROM (
    SELECT
         Staff      = CASE  WHEN [JobTitle] LIKE '%chief%' OR [JobTitle] LIKE '%vice president%' THEN 'Upper Management'
                            WHEN [JobTitle] LIKE '%manager%'    THEN 'Middle Management'
                            WHEN [JobTitle] LIKE '%senior%'     THEN 'Senior Staff'
                            ELSE 'Staff' END
    FROM [AdventureWorks2017].[HumanResources].[Employee]
) tmp
GROUP BY Staff
HAVING COUNT(Staff) > 10
ORDER BY [StaffCount];

Because we defined the Staff column in the subquery, we can refer to it in the outer query. This makes re-using code much easier and it makes the SQL statement more compact and easier to maintain. If we would like to change the definition of Staff, we would need to alter it at only one location.

CASE can also be used in a SET statement, to assign a value to a variable:

SET DATEFIRST 1;
DECLARE @isitfridayyet VARCHAR(100);
SET @isitfridayyet = CASE WHEN DATEPART(WEEKDAY,GETDATE()) = 5
                            THEN 'It''s Friday!'
                            ELSE 'It''s not yet Friday...' END
 
PRINT @isitfridayyet;

To finish up, let’s take a look at CASE functionality inside an UPDATE statement. The employees deserve a bonus in the form of extra vacation days. If you don’t have many left, you get 3 extra days. If you have between 10 and 20 hours left, you get 2 extra days, otherwise you get 1 extra day. This can be written with the following UPDATE statement:

UPDATE [AdventureWorks2017].[HumanResources].[Employee]
SET [VacationHours] = CASE  WHEN [VacationHours] < 10 THEN [VacationHours] + 24 -- 3 more days
                            WHEN [VacationHours] < 20 THEN [VacationHours] + 16 -- 2 more days
                            ELSE [VacationHours] + 8 END; -- 1 more day

Be aware that this statement will update every row of the employee table. If you just want to test it out, you can wrap it inside a transaction and roll it back at the end.

Next Steps

About the author