SQL Server T-SQL CASE Statement Examples


By:   |   Updated: 2021-04-20   |   Comments (5)   |   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];
searched case on string

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];
searched case without ELSE

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];
searched case on date

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];
simple case with integers

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];
data type error in CASE

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];
multiple WHERE

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];
nested CASE

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];
simple case example

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];
IIF example

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.

CASE in 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];
case in group by

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];
result set

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


Last Updated: 2021-04-20


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips



Comments For This Article




Monday, May 17, 2021 - 2:44:26 PM - Koen Verbeeck Back To Top (88694)
Hi Fiander,
your expression currently doesn't work, because MaritalStatus is only one character long, so all other columns are converted to this data type, which leads to incorrect results (all results are M). The following should work:

ISNULL(NULLIF(ISNULL(NULLIF(CONVERT(VARCHAR(50),[MaritalStatus]),'S'), 'Single'),[MaritalStatus]), 'Married' )

However, I've never understood the notion that CASE would be RBAR and thus inherently slow. As far as I know, this is not the case. I've blown up the Employee table to a little more than a million rows, so I could test performance between the two statements. The execution plans are almost exact the same, and both get 50% of the cost.

The results (CASE is first, isnull(nullif()) second):

(1187840 rows affected)
Table 'Employee_BIG'. Scan count 1, logical reads 26633, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
CPU time = 719 ms, elapsed time = 8456 ms.

(1187840 rows affected)
Table 'Employee_BIG'. Scan count 1, logical reads 26633, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
CPU time = 1109 ms, elapsed time = 9230 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

As you can see, logical reads are exactly the same. The second query is a little bit slower, but this might just be noise.
The CASE solution however is much more readable and thus easier to maintain.

Regards,
Koen

Monday, May 17, 2021 - 10:45:41 AM - Fiander Back To Top (88692)
MaritalStatusDesc = CASE WHEN [MaritalStatus] = 'S' THEN 'Single' ELSE 'Married' END

Can be changed into this:
MaritalStatusDesc = ISNULL(NULLIF(ISNULL(NULLIF([MaritalStatus],'S'), 'Single'),[MaritalStatus]), 'Married' )

NULLIF and ISNULL are set based, and not RBAR ( row by agonizing row ) and by this a lot more performant.
Try this on a table selecting a few miljon rows

i do always keep the original case as comment for readability :-)

Friday, May 14, 2021 - 1:14:25 PM - Raghavendra Back To Top (88685)
Nice collection of scenarios and use of Case statements. Yet another usage would be good to share.

SELECT
[JobTitle], [Gender],
sum(CASE WHEN [BirthDate] < '1965-01-01' THEN 1 ELSE 0 END) as BoomersCount,
sum(CASE WHEN [BirthDate] < '1965-01-01' THEN 0' ELSE 1 END) as NotBoomerCount ,
FROM [AdventureWorks2017].[HumanResources].[Employee]
group by [JobTitle], [Gender]

Thursday, April 22, 2021 - 3:12:36 AM - Koen Verbeeck Back To Top (88584)
Hi Joe,

thanks for reading and commenting. I agree 100%. I do mention in the article that NULL values will be returned and that it's a best practice to always write an ELSE clause. Maybe I should've been more explicit. Some tools like SQLPrompt even give a warning label when you leave out the ELSE clause.

Regards,
Koen

Wednesday, April 21, 2021 - 1:17:59 PM - Joe F Celko Back To Top (88581)
>> The ELSE argument is optional. <<

Yes, but you want to tell people that there is a default "ELSE NULL" implicit when it is left off. Always writing the else clause is just good programming, since it documents what's actually happening and give you location in case you would to replace that implicit null with an explicit value.


download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

SQL Server DROP TABLE IF EXISTS Examples














get free sql tips
agree to terms