Using the CASE Statement
in SQL Server

The T-SQL 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 T-SQL 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.

T-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.

SQL CASE statement with multiple conditions

In case you need the result to satisfy multiple conditions, you can add those conditions to the T-SQL 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

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

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

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

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 keywords pairs
Quick info about database objects
Instant code formatter with built-in formatting profiles

Using the CASE Statement
in SQL Server

The T-SQL CASE Statement fundamentals

Enjoying CASE expression with
SQL Complete

dbForge SQL Complete

Enjoy even the most complex CASE expressions with SQL Complete

T-SQL CASE statement types

SQL CASE statement with multiple conditions

Using CASE statements with the ORDER BY clause

Using CASE with the GROUP BY clause

Insert statement with CASE
in SQL

UPDATE statement with
T-SQL CASE

Use CASE WHEN with SUM()
in SQL

T-SQL CASE statement best practices

With SQL Complete, you GET

SQL Complete

Advanced solution for SQL database development, management, and administration

Using the CASE Statement in SQL Server

The T-SQL CASE Statement fundamentals

Enjoying CASE expression with SQL Complete

dbForge SQL Complete

Enjoy even the most complex CASE expressions with SQL Complete

T-SQL CASE statement types

SQL CASE statement with multiple conditions

Using CASE statements with the ORDER BY clause

Using CASE with the GROUP BY clause

Insert statement with CASE in SQL

UPDATE statement with T-SQL CASE

Use CASE WHEN with SUM() in SQL

T-SQL CASE statement best practices

With SQL Complete, you GET

SQL Complete

Advanced solution for SQL database development, management, and administration

Using the CASE Statement
in SQL Server

Enjoying CASE expression with
SQL Complete

Insert statement with CASE
in SQL

UPDATE statement with
T-SQL CASE

Use CASE WHEN with SUM()
in SQL