CASE Expression Pitfalls: NULL Tests and Data Type Precedence

A CASE expression looks like a small if statement, but SQL rules still govern its comparisons and result type. WHEN NULL does not match a NULL value, mixed numeric and text branches can fail conversion, and a missing ELSE returns NULL. Each trap has a short, testable rewrite.

Distinguish Simple and Searched CASE

A simple CASE compares one input value with each WHEN value using equality. A searched CASE evaluates a predicate in each WHEN. Since NULL = NULL is UNKNOWN, a simple WHEN NULL does not match a NULL input. Use a searched WHEN x IS NULL instead.

DECLARE @status varchar(10) = NULL;
SELECT CASE @status
         WHEN NULL THEN 'missing'
         ELSE 'not missing'
       END AS simple_result,
       CASE WHEN @status IS NULL THEN 'missing'
            ELSE 'not missing' END AS searched_result;

The first result is not missing; the second is missing. I use this example when a classification report assigns unknown statuses to the wrong bucket. What should a missing status mean in the business rule? Write that explicitly before choosing the CASE form.

A CASE Expression Returns One Type

Every THEN and ELSE branch contributes to one result expression, so SQL Server chooses a common data type using precedence rules. An int branch and a varchar branch do not create a column that changes type by row. A nonnumeric text branch is converted toward int and raises an error, even when the author expected the text to remain text. The next block fails on purpose to show it.

DECLARE @is_ready bit = 0;
-- Fails: 'pending' cannot convert to int (Msg 245)
SELECT CASE WHEN @is_ready = 1 THEN 1
            ELSE 'pending' END AS mixed_result;

The fix is to choose one intended output type. For a display label, convert the numeric branch to text. For a numeric status code, use numeric values in every branch and map labels separately. Avoid broad TRY_CONVERT in the final CASE merely to silence a design mismatch.

DECLARE @is_ready bit = 0;
SELECT CASE WHEN @is_ready = 1 THEN '1'
            ELSE 'pending' END AS display_label;

Give the Default Branch a Meaning

Without ELSE, CASE returns NULL when no WHEN condition matches. That can be correct when NULL means unknown, but it should be deliberate. A SUM or COUNT over the result can skip those rows and quietly change a report. Use an explicit ELSE for a complete classification, and test an unexpected source value.

DECLARE @priority varchar(10) = 'urgent';
SELECT CASE @priority
         WHEN 'high' THEN 3
         WHEN 'medium' THEN 2
         WHEN 'low' THEN 1
       END AS without_else,
       CASE @priority
         WHEN 'high' THEN 3
         WHEN 'medium' THEN 2
         WHEN 'low' THEN 1
         ELSE 0
       END AS with_else;

The first result is NULL, the second is 0. Whether 0 is a valid fallback depends on the application. In a data-quality pipeline, it can be better to keep NULL and count unknown categories separately than to fold them into low priority. The point is to make the choice visible.

Know What a CASE Expression Evaluates First

CASE returns the result of the first WHEN condition that evaluates TRUE, but it is not a universal short-circuit shield. SQL Server can evaluate aggregates or other inputs before CASE selects a branch. Do not rely on CASE to prevent a divide-by-zero or conversion error in a calculation that the optimizer can compute earlier. Use NULLIF around a divisor and TRY_CONVERT only where invalid input is an expected data condition.

DECLARE @numerator decimal(12,2) = 100.00,
        @denominator decimal(12,2) = 0.00;
SELECT @numerator / NULLIF(@denominator,0) AS safe_ratio;

The result is NULL instead of an error. Decide whether that is the required business output, and surface missing ratios in the report. A safe divisor is more reliable than assuming one CASE branch will prevent all evaluation elsewhere in the plan.

Keep a Nested CASE Expression Readable

SQL Server limits CASE expressions to ten levels of nesting. Long nested trees are hard to review well before they hit that limit. A lookup table, ordered rules table, or staged calculation can make the mapping clearer. Be careful with rule precedence: a table-driven rewrite must still choose the same first matching condition.

I have seen a classification CASE grow one product exception per release until nobody could explain the default path. I extract sample inputs for each branch and test them before refactoring. If the logic is a finite code-to-label mapping, a joined lookup table is usually easier to maintain than a stack of nested CASE expressions.

Test All Branches and Types

Build a small test set with NULL, expected values, unexpected values, and boundary numeric values. Inspect the result metadata as well as output rows. A CASE that appears correct for one parameter can still fail when another branch supplies a nonconvertible text value. Keep the desired output type in the specification.

SELECT SQL_VARIANT_PROPERTY
(
    CASE WHEN 1=1 THEN CONVERT(varchar(10),'ready')
         ELSE CONVERT(varchar(10),'pending') END,
    'BaseType'
) AS result_type;

I review a CASE change with the same care as a table constraint because it classifies data users act on. The syntax is compact; the semantics deserve explicit tests. A correct rewrite handles NULL as a predicate, chooses one result type, and states the default outcome.

Check Implicit Conversion Before Production

The data type selected for a CASE result follows precedence rules, not the order of the branches. That means a query can compile and work for common rows, then fail when a rare text branch is selected or evaluated. Use SQL_VARIANT_PROPERTY or inspect result metadata in a test query, and cast each branch to the intended type. For display output, explicit CONVERT(varchar(20), numeric_value) makes the choice obvious. For calculations, keep the CASE result numeric and join to a label separately.

I also check length. A varchar branch of length 5 and another of length 100 can affect inferred result width, memory grants, or truncation after assignment into a smaller target. An explicit CAST with an appropriate length is easier to review than relying on inference across many branches.

Distinguish Missing From Zero

A missing ELSE returning NULL can be useful when it means “unclassified.” Replacing it with zero changes the meaning. In an aggregate, COUNT(CASE WHEN condition THEN 1 END) counts matches, while COUNT(CASE WHEN condition THEN 1 ELSE 0 END) counts every non-NULL row. That difference has caused report errors more than once. Test COUNT and SUM expressions with matched and unmatched rows before changing the default.

Keep Branches in Priority Order

In a searched CASE, the first true WHEN wins. A broad condition placed before a narrow exception can make the exception unreachable. Write a test row for each branch and review overlap. If the conditions form mutually exclusive categories, document the boundaries with explicit comparison operators. A compact CASE is only as trustworthy as its branch order and input contract.

Related reading on this blog: SQL Puzzle: Unsolved CASE Expression and What is Alternative to CASE Statement in SQL Server? IIF Function: Interview Question of the Week #164.

CASE is not a type-free branch, it is one expression with NULL and result-type rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.