SQL Server CHOOSE(): Simple Enough, Until It Isn't

SQL Server has quite a few built-in functions that don't get much attention, and CHOOSE() is one of them. It's been around since SQL Server 2012, but I don't really see it used too often.

The function is straightforward. Give it a position and a list of values, and it returns the value at that position. In some cases, it can eliminate a lengthy CASE expression and make the query much easier to read.

There are a few things worth knowing before using it, though. The positions start at 1, not 0, an invalid position returns NULL, and SQL Server's data type precedence rules still apply. Those details matter, particularly when the position comes from data rather than a value you've supplied yourself.

Let's look at where CHOOSE() makes sense, how it compares with CASE, and a few examples where the results might not be what you expected.

What CHOOSE() Does

The syntax is simple: CHOOSE(index, value1, value2, ...). The index identifies which item in the list to return.

SELECT
    CHOOSE(1, 'Development', 'Test', 'Production') AS Environment1,
    CHOOSE(2, 'Development', 'Test', 'Production') AS Environment2,
    CHOOSE(3, 'Development', 'Test', 'Production') AS Environment3;

For a simple positional selection, CHOOSE() can be easier to read than a CASE expression. Both return the same result when the index is 1, 2, or 3. Here's a quick comparison using an EnvironmentID of 2:

DECLARE @EnvironmentID int = 2;

SELECT
    CASE @EnvironmentID
        WHEN 1 THEN 'Development'
        WHEN 2 THEN 'Test'
        WHEN 3 THEN 'Production'
    END AS CaseResult;

SELECT CHOOSE(@EnvironmentID, 'Development', 'Test', 'Production') AS ChooseResult;

You can change @EnvironmentID to 1 or 3 and see that both expressions still return the same result.

That's where CHOOSE() earns its place. It can make a straightforward positional lookup compact without making it difficult to follow. But it isn't a replacement for every CASE expression, especially when conditions are more complex or the values aren't sequential.

The Index Starts at 1

The first detail to remember is that CHOOSE() uses a 1-based index.

Suppose a table stores status codes beginning at zero. A CASE expression might look like this:

CASE StatusID
    WHEN 0 THEN 'Unknown'
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Temp'
END

Replacing it directly with CHOOSE(StatusID, 'Unknown', 'Active', 'Inactive', 'Temp') would not produce the same results. Here's a small example using rows rather than individual function calls:

SELECT
    s.StatusID,
    CHOOSE(s.StatusID, 'Unknown', 'Active', 'Inactive', 'Temp') AS DirectChoice,
    CHOOSE(s.StatusID + 1, 'Unknown', 'Active', 'Inactive', 'Temp') AS CorrectedChoice
FROM (VALUES (0), (1), (2), (3)) AS s(StatusID);

Adding 1 to the index works here because the codes are consecutive and start at zero. If the codes aren't consecutive, or their meaning can change, a CASE expression or a proper lookup table is generally a better fit.

What Happens Outside the List?

If the index is less than 1 or greater than the number of values supplied, CHOOSE() returns NULL. A NULL index also returns NULL.

SELECT
    CHOOSE(0, 'Dev', 'Test', 'Prod') AS IndexZero,
    CHOOSE(4, 'Dev', 'Test', 'Prod') AS PastTheEnd,
    CHOOSE(-1, 'Dev', 'Test', 'Prod') AS NegativeIndex,
    CHOOSE(NULL, 'Dev', 'Test', 'Prod') AS NullIndex;

That isn't necessarily a problem. A CASE expression without a matching branch or an ELSE also returns NULL. The important thing is to decide whether NULL is an acceptable result for the data you're working with. If you need an explicit fallback, CASE makes that very clear.

What If the Index Has a Decimal?

CHOOSE() expects an index that can be converted to INT. A numeric value with a fractional portion is converted to an integer, which truncates rather than rounds the value.

SELECT
    CHOOSE(2.9, 'Development', 'Test', 'Production') AS SelectedEnvironment;

The index becomes 2, so the second item is returned. That's predictable once you know the conversion rule, but worth checking if the index is calculated rather than stored as an integer.

Data Type Precedence Still Applies

The values supplied to CHOOSE() don't have to be written with the same data type, but SQL Server must determine a single return type. It uses the type with the highest precedence among the supplied values, just as it does for a CASE expression.

SELECT
    CHOOSE(2, 100, '200', '300') AS NumericChoice; 

In that last example, int has higher precedence than varchar, so the expression's result type is INT. The selected value, 100, is already an integer.

Now select a string that cannot be converted to an integer:

SELECT
    CHOOSE(2, 100, 'Active', 'Inactive') AS MixedTypes;

The second value is selected, but SQL Server has determined that the result must be an int. Converting 'Active' to INT fails. The lesson isn't that CHOOSE() can't handle mixed types; it's that implicit conversion rules still matter. When the values represent the same kind of information, use consistent data types.

When Would I Use CHOOSE()?

I like CHOOSE() for a short, fixed list when the index is known to be within range. One example is selecting a month label from a month number:

SELECT
    CHOOSE(MONTH(GETDATE()),
           'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
           'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS MonthAbbreviation;

MONTH() returns a number from 1 through 12 for a valid date, so its range lines up with the twelve items in the list. For this kind of small, fixed selection, CHOOSE() is concise and easy to understand.

For status codes with gaps, complicated conditions, or values maintained as business data, I'd use CASE or a lookup table instead. And if you're converting an existing CASE expression to CHOOSE(), make sure the numeric values really do correspond to positions in the list.

CHOOSE() isn't complicated, and there's no reason to avoid it. For a short, fixed list of values, it can be considerably easier to read than a CASE expression. Like most T-SQL functions, it has a few behaviors worth understanding, but once you know them, it's another clever option to use.

More to Read

MSFT -- CHOOSE (Transact-SQL)
MSFT -- IIF (Transact-SQL)
MSFT -- CASE (Transact-SQL)
MSFT -- Data type precedence (Transact-SQL)