`CASE`: Express decisions within a query

A query should not always return the value exactly as it is stored. Sometimes we need to classify an existence, translate a state, or calculate a different value based on a condition.

CASE evaluates those conditions and produces a result. An expression is a combination of values and operations that returns a value. CASE does not control the procedure’s overall flow—which statements run; it builds an expression that can be used in SELECT, ORDER BY, UPDATE, and other statements.

The idea

CASE checks its conditions in the order written and returns the result of the first one that is met. If none match, it uses ELSE. When we omit ELSE, the result is NULL, the marker for an absent or unknown value.

This means that order matters. A broad condition placed first may prevent a more specific condition from being evaluated.

Syntax

CASE
    WHEN Condicion1 THEN Resultado1
    WHEN Condicion2 THEN Resultado2
    ELSE ResultadoAlternativo
END

There is also the simple form, useful when we compare a single expression with several values:

CASE Expresion
    WHEN Valor1 THEN Resultado1
    WHEN Valor2 THEN Resultado2
    ELSE ResultadoAlternativo
END

Example: classify existence

We want to show a more useful description than the isolated quantity:

SELECT
    p.IdProducto,
    p.Nombre,
    p.Existencia,
    CASE
        WHEN p.Existencia = 0 THEN 'Sin existencia'
        WHEN p.Existencia <= 5 THEN 'Existencia baja'
        ELSE 'Disponible'
    END AS EstadoExistencia
FROM dbo.Producto AS p
ORDER BY p.Existencia;

Name	Existence	ExistenceState
Compact keyboard	0	Without existence
1 TB external drive	4	Low stock
Monitor stand	7	Available

The row with zero existence also matches <= 5, but keeps the first match: Sin existencia.

Compare a single value

When all the alternatives depend on the same column we can use the simple form:

SELECT
    p.IdPedido,
    p.Estado,
    CASE p.Estado
        WHEN 'Pendiente' THEN 'Requiere atención'
        WHEN 'Confirmado' THEN 'En preparación'
        WHEN 'Enviado' THEN 'En tránsito'
        WHEN 'Cancelado' THEN 'Sin seguimiento'
        ELSE 'Estado desconocido'
    END AS DescripcionEstado
FROM dbo.Pedido AS p;

This way compares by equality. If we need ranges, multiple columns, or conditions with AND and OR, we use the first syntax.

Use `CASE` when ordering

We can also set a priority that does not exist alphabetically:

SELECT
    p.IdPedido,
    p.Estado,
    p.FechaPedido
FROM dbo.Pedido AS p
ORDER BY
    CASE p.Estado
        WHEN 'Pendiente' THEN 1
        WHEN 'Confirmado' THEN 2
        WHEN 'Enviado' THEN 3
        ELSE 4
    END,
    p.FechaPedido;

What should be checked

The results of all branches must be convertible to a compatible type. Type precedence is the order SQL Server uses to decide which type different values will be converted to. Mixing numbers, dates, and text without considering it can cause unexpected conversions or errors.

CASE also does not guarantee that a risky expression is protected in every context. If an operation can fail, such as a conversion or division, make it safe directly. TRY_CONVERT returns NULL when an allowed conversion cannot be completed; NULLIF returns NULL when its two arguments are equal.