Using the CASE Statement
in SQL Server

The CASE Statement fundamentals

What is CASE in SQL?

SQL CASE statement evaluates a condition and returns a result that meets that condition. If none of the conditions is evaluated to TRUE it returns a value from the ELSE block. In simple words, the CASE expression is the way to build the IF - THEN logic into SQL.

A quick review of CASE rules
  • CASE must be followed by at least one WHEN... THEN expression
  • Every CASE statement must end with the END keyword
  • The ELSE argument is optional
  • CASE can be used in any statement or clause that allows a valid expression
  • Only 10 levels of nesting are allowed in SQL Server
Syntax example

The syntax for the SELECT statement with a simple CASE expression is as follows:

SELECT CASE expression
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result
END
                                    

Enjoying CASE expression with

SQL Complete

Being a declarative language, SQL provides little to no control over how inputs are dealt with. CASE expressions add much-needed flexibility to SQL queries and are crucial to master and use correctly.

That's where SQL Complete hits the stage. Its advanced autocompletion capabilities allow writing the most complex CASE expressions quickly. In-built code snippets for CASE statements save your energy, highlighting pairs of matching keywords and quick navigation between them help minimize coding time as SQL CASE expressions can be long enough and combing through them is not an easy task.

SQL Complete also boasts a robust formatter that allows making code with CASE statements more readable and can help enforce common coding standards to a team or organization.


As part of our SQL CASE expression tutorial, we'll have a look at different MSSQL CASE statement examples with the help of the SQL Complete tool.

dbForge SQL Complete

Enjoy even the most complex CASE expressions with SQL Complete

SQL CASE statement types

There are two types of SQL CASE statements: a simple CASE statement and a searched CASE statement.

Simple CASE statements determine the result value evaluating an expression against multiple values.

Searched CASE statements determine the result value evaluating a set of Boolean expressions.

Types of SQL query CASE WHEN

SQL CASE statement with multiple conditions

In case you need the result to satisfy multiple conditions, you can add those conditions to the CASE statement and combine them with the AND operator:

CASE expression
  WHEN condition1 AND condition2 THEN result1 
  ELSE result2 
END
                                    

You can also define a number of result values in the CASE statement by including as many WHEN/THEN statements as you want:

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     WHEN condition3 THEN result3
     ...
     ELSE resultN
END
                                    
SQL CASE WHEN multiple conditions

Using CASE statements with the ORDER BY clause

CASE statements in the ORDER BY clause are used to change the output order and instruct the query to sort the results based on a certain requirement.

SELECT * FROM table_name
ORDER BY
       CASE expression
         WHEN condition1 THEN result1,
         WHEN condition2 THEN result2
            ...
         ELSE resultN
       END
                                    
SQL Server ORDER BY CASE

Using CASE with the GROUP BY clause

To apply aggregate functions, you can use the CASE statement in conjunction with the GROUP BY clause.

Grouping with the CASE expression is a simple yet elegant method to arrange the query output in the required way.

SELECT 
       CASE WHEN condition1 THEN result1
            ELSE result2 END AS condition,
       COUNT(1) AS count
  FROM table_name
  GROUP BY CASE WHEN condition1 THEN result1
                ELSE result2 
END
                                    
Using CASE inside of aggregate functions

Insert statement with CASE
in SQL

You can use the CASE expression to insert data into a SQL Server table. The INSERT statement with CASE will scan for the required values and if found, insert values from THEN expression.

INSERT INTO column_name
VALUES (CASE expression
            WHEN condition1 THEN result1
            WHEN condition2 THEN result2
            ELSE result3
        END);
                                    
Update with Case statement in set clause

UPDATE statement with
SQL CASE

In SQL, you can update a table with the CASE statement.

The CASE expression used in the UPDATE statement allows selecting or setting a new value based on the input conditions.

UPDATE table_name
    SET column_value =
            CASE WHEN condition1 THEN result1,
                 WHEN condition2 THEN result2
                 ELSE result3
            END
                                    
Update with Case statement in set clause

Use CASE WHEN with SUM()
in SQL

The CASE WHEN expression is quite often used with the SUM() function for more complex reporting.

How does it work? The CASE expression assigns values according to the specified conditions and then the SUM function sums all those values.

SELECT COUNT(*) AS TotalCount,
    SUM(CASE WHEN condition1 
             THEN result1 
             ELSE result3 
        END) AS column_name1,
    SUM(CASE WHEN condition1 
             THEN result2 
             ELSE result3 
        END) AS column_name2
FROM table_name
                                    
SQL SUM CASE multiple conditions
SELECT CASE limitations in SQL

SQL CASE statement best practices

  • Multiple conditions in the CASE statements are processed in a sequential model. It stops checking conditions with the first successful condition.
  • It is better to use the ELSE block in CASE statements so that if none of the conditions is not satisfied, the default value will be returned.
  • CASE statement can not be used for checking NULL values in a table.
  • It is recommended to avoid conflicting conditions.

With SQL Complete, you GET

  • Code snippets for CASE expressions
  • Context-based prompts for table and column names
  • Navigation between the CASE and END keywords
  • Highlighting of matching keywords pairs
  • Quick info about database objects
  • Instant code formatter with built-in formatting profiles

SQL Complete

Advanced solution for SQL database development, management, and administration