Using the CASE Statement
in SQL Server

In databases, stored data often requires additional manipulation to produce meaningful results. To support this, SQL provides dedicated tools, one of which is the CASE statement. It allows you to create new columns or transform existing data based on specified conditions. This article will explore the CASE statement in SQL Server and examine its usage across a variety of scenarios.

What is CASE in SQL?

The CASE expression in a SQL query is used to implement IF-THEN-ELSE logic. It lets you define different results based on conditions directly inside your statements, such as SELECT, UPDATE, INSERT, or ORDER BY. If no condition matches, the value from the ELSE clause is returned.

This approach keeps your code concise by embedding conditional logic in SQL, without needing extra procedural blocks.

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

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

How to use CASE in SQL queries

Once you understand what CASE does, the next step is knowing where to apply it. The expression is most valuable when you need to return conditional values directly within a query. For example, you can adjust column outputs in a SELECT, group results more flexibly in a GROUP BY, or apply conditional assignments in UPDATE and INSERT statements. This versatility is the reason why the CASE expression is considered a core part of writing dynamic, production-ready SQL queries.

CASE WHEN syntax in SQL Server

The WHEN...THEN pattern is at the heart of every CASE expression in T-SQL. Each condition is tested in sequence, and the first one that evaluates to TRUE determines the result.

The syntax for the SELECT statement with a simple CASE expression is commonly explained using CASE WHEN statements in SQL.

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 T-SQL CASE expressions quickly. In-built code snippets for CASE statements highlight pairs of matching keywords, while quick navigation between them helps minimize coding time.

SQL Complete also boasts a robust formatter that allows making code with CASE statements more readable and enables you to enforce common coding standards for 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 dbForge SQL Complete.

Types of CASE statements in T-SQL

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.

SQL CASE statement with multiple conditions

When the result must satisfy multiple conditions, add all conditions to the T-SQL CASE statement and combine them using AND:

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

Nested CASE in SQL (CASE inside CASE)

You can also place one CASE expression inside another, creating a nested CASE expression. This allows you to handle multiple layers of conditional logic within the same query.

For example, use SQL CASE where a user's status is marked Active and nest another CASE to separate recent logins from older ones.

SELECT
  CASE
    WHEN status = 'A' THEN CASE
        WHEN last_login > '2025-01-01' THEN 'Active - Recent'
        ELSE 'Active - Old'
      END
    ELSE 'Inactive'
  END AS user_status
FROM users;

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 specific requirements.

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

Using CASE with the GROUP BY clause

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

The GROUP BY clause with CASE in SQL is often used to categorize rows dynamically, making it easier to count or summarize data based on conditions. Here's a simple SQL GROUP BY CASE example.

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

Using CASE in INSERT statements

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 the 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

Using CASE in UPDATE statements

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

Using CASE with UPDATE SET

When CASE is used in the SET clause of an UPDATE statement, the query effectively becomes an UPDATE SET … CASE construction.

Developers often use CASE in the SET clause to assign values conditionally. Queries written this way are sometimes shortened to SQL UPDATE SET CASE or explained in examples as SQL UPDATE with CASE statement in SET clause.

UPDATE table_name
  SET column_value =
    CASE WHEN condition1 THEN result1
         WHEN condition2 THEN result2
         ELSE result3
    END;

CASE WHEN with aggregates (SUM)

The CASE WHEN expression is often combined with aggregates such as SUM() for conditional reporting.

In this scenario, CASE assigns values according to the specified conditions, and SUM totals 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;

CASE compatibility across SQL Server versions

CASE expressions are fully supported across SQL Server editions and versions. Importantly, the ability to use CASE within UPDATE statements has been available since SQL Server 2008.

In other words, an UPDATE CASE SQL Server 2008 query will still run correctly in today's environments. This backward compatibility ensures that once you learn the syntax, you can rely on it across both legacy systems and the latest releases.

T-SQL CASE statement best practices

With multiple conditions, CASE statements evaluate them one by one and stop at the first successful condition.
It is better to use the ELSE block in CASE statements so that if none of the conditions is satisfied, the default value will be returned.
A CASE statement cannot 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 keyword pairs
Quick info about database objects
Instant code formatter with built-in formatting profiles

Using the CASE Statement in SQL Server