CHOOSE: Handle Indexes Outside the List

CHOOSE selects a value by a one-based index, and unsupported indexes need an explicit handling decision. I use it for small fixed lists when positions are part of the contract. A typed CASE expression is clearer when the fallback needs a visible business meaning.

Start counting at one

The three choices in this example are Mail, Push and Batch. Index 1 selects Mail, while index 3 selects Batch. Index zero does not select the first item.

The input includes negative, zero and out-of-range indexes. It also includes a NULL index. Those cases expose the boundaries instead of testing only the three successful selections.

The expected CHOOSE values for the invalid positions are NULL. That output does not explain why the position was unsupported. An application still needs to decide whether the missing selection should be allowed or rejected.

I keep the index typed as int in this example. CHOOSE can convert another numeric index type to an integer. Using an explicit integer avoids making conversion behavior part of an unrelated selection rule.

WITH Indexes AS
(
    SELECT CaseId,ChoiceIndex
    FROM (VALUES (1,CAST(-1 AS int)),(2,0),(3,1),(4,2),(5,3),(6,4),(7,NULL))
        AS v(CaseId,ChoiceIndex)
)
SELECT CaseId,ChoiceIndex,
    CHOOSE(ChoiceIndex,CAST(N'Mail' AS nvarchar(12)),
        CAST(N'Push' AS nvarchar(12)),CAST(N'Batch' AS nvarchar(12))) AS ChosenValue,
    CASE ChoiceIndex
        WHEN 1 THEN CAST(N'Mail' AS nvarchar(12))
        WHEN 2 THEN CAST(N'Push' AS nvarchar(12))
        WHEN 3 THEN CAST(N'Batch' AS nvarchar(12))
        ELSE CAST(N'Unsupported' AS nvarchar(12))
    END AS CaseValue
FROM Indexes
ORDER BY CaseId;

Make the fallback visible

The CASE expression selects the same three supported values. Its ELSE branch returns Unsupported for every other input. The column makes the handling choice visible beside the CHOOSE result.

That fallback is a sample policy rather than a universal recommendation. A NULL index can represent missing input instead of an invalid selection. If the application distinguishes those cases, give them separate branches.

I’d avoid replacing missing values with a normal choice such as Mail. That makes incomplete input look valid. A visible fallback or separate validation result preserves the distinction for the caller.

The expected rows demonstrate the policy without changing any data. Cases 3, 4 and 5 select the supported labels. Cases 1, 2, 6 and 7 display NULL beside Unsupported.

Keep the choices type compatible

CHOOSE uses data type precedence across its choices. It does not promise that only the selected value determines the result type. Combining unrelated numeric and textual choices can introduce conversions.

I explicitly cast every label to nvarchar with the same length. The CASE branches and fallback use that same type too. This example therefore tests selection boundaries without relying on implicit conversion.

A typed CASE expression does not erase type precedence rules. Its branches still need compatible types. I choose it here for an explicit fallback, not as a shortcut around SQL’s type system.

Choose a length that fits every legitimate result, including the fallback. A cast to an undersized text type can shorten the message. The twelve-character type in this sample accommodates every displayed label.

Use positions only when they are stable

I’m tempted to use CHOOSE anywhere a short mapping appears. That becomes fragile when the meaning of index 2 changes between systems. A stable business code can be clearer than an undocumented position.

For a fixed list controlled by the application, positional selection stays compact. For a growing maintained mapping, a reference table can express the relationship more clearly. That is a design decision beyond this read-only example.

The query uses a separate CaseId for deterministic display order. The ChoiceIndex retains its original NULL or boundary value. That keeps the test identity separate from the selected position.

These examples concern returned values, not execution speed. I’d review the supported inputs, fallback policy and return type before comparing syntax length. A shorter expression is useful only when its handling remains understandable.

Small lists are handy, and a clear fallback keeps them honest.

A fallback is not a default business choice, it is an explicit policy for unsupported input.

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