Applies to SQL Server 2019, 2022 and 2025 (and Azure SQL Database and Managed Instance).

The T-SQL CHOOSE function returns one value from a list, picked by its position. It has been around since SQL Server 2012 and I mostly use it to turn a small number into a label: a month number into a season, a status code into text, a weekday number into a shift name. It is short and readable, but it has three behaviours that catch people out, and one of them can give you wrong answers without any error.

How the T-SQL CHOOSE function works

CHOOSE ( index , value_1 , value_2 [ , value_n ] )

The index is 1-based: 1 returns the first value, 2 the second, and so on.

SELECT CHOOSE(2, 'Bronze', 'Silver', 'Gold') AS Tier;   -- Silver

-- Quarter label from a date
SELECT OrderDate,
       CHOOSE(DATEPART(QUARTER, OrderDate), 'Q1', 'Q2', 'Q3', 'Q4') AS Qtr
FROM dbo.Orders;

Out of range gives NULL, not an error

An index of 0, a negative number or anything past the end of the list returns NULL. SQL Server does not warn you.

SELECT CHOOSE(0, 'Bronze', 'Silver', 'Gold') AS ZeroBased,    -- NULL
       CHOOSE(4, 'Bronze', 'Silver', 'Gold') AS PastTheEnd,   -- NULL
       CHOOSE(2.9, 'Bronze', 'Silver', 'Gold') AS Truncated;  -- Silver

Two things to notice. Anyone used to 0-based arrays will be one out. And a non-integer index is converted to int, which truncates rather than rounds, so 2.9 becomes 2. If a NULL result would be a problem, wrap the call in ISNULL or COALESCE with a sensible default, or check the range first.

The return type comes from the whole list

CHOOSE returns the data type with the highest precedence among all the values in the list, not the type of the one it picks. Mix numbers and strings and SQL Server tries to convert every string to a number:

SELECT CHOOSE(1, 'None', 1, 2);
-- Msg 245: Conversion failed when converting the varchar value 'None' to data type int.

Keep every value in the list the same type, or cast them explicitly:

SELECT CHOOSE(1, 'None', CAST(1 AS varchar(10)), CAST(2 AS varchar(10)));   -- None

The common mistake: a CHOOSE index that changes each time

This is the one that gives wrong answers without an error. CHOOSE is shorthand: the optimiser expands it into a CASE expression, one WHEN per value, and each WHEN works out the index again. That is fine for a column or a fixed value. But if the index is non-deterministic, such as anything built on NEWID() or RAND(), each WHEN can see a different number.

-- Looks like it always returns Red, Green or Blue
SELECT TOP (1000)
       CHOOSE(ABS(CHECKSUM(NEWID())) % 3 + 1, 'Red', 'Green', 'Blue') AS Colour
INTO #c
FROM sys.all_objects;

SELECT Colour, COUNT(*) AS RowCnt
FROM #c
GROUP BY Colour;   -- you will usually see some NULLs

Behind the scenes it runs as CASE WHEN idx = 1 THEN ‘Red’ WHEN idx = 2 THEN ‘Green’ WHEN idx = 3 THEN ‘Blue’ END, and idx is a new random number in every WHEN. So it is possible for none of them to match. Moving the expression into a derived table or CROSS APPLY does not reliably help, because the optimiser is free to copy it back into each WHEN. The safe fix is to store the index first, then use the stored value:

SELECT TOP (1000) ABS(CHECKSUM(NEWID())) % 3 + 1 AS idx
INTO #i
FROM sys.all_objects;

SELECT CHOOSE(idx, 'Red', 'Green', 'Blue') AS Colour
FROM #i;   -- never NULL

For a single value, a variable does the same job: DECLARE @idx int = ABS(CHECKSUM(NEWID())) % 3 + 1; then CHOOSE(@idx, ...). The same applies to a scalar subquery used as the index: it can run once per WHEN, which costs time even when the answer is right, so work it out once first.

When I use CHOOSE and when I don’t

• Use it for short, fixed lists keyed on a small number that is already in the row: month, quarter, weekday, a status code.
• Use a lookup table instead when the list is long, changes or is needed in more than one query. Then it is data, not code.
• Use CASE when the test is anything other than “equals 1, 2, 3”, such as ranges.

Watch out for

• The index is 1-based, and out-of-range values give NULL without an error.
• A decimal index is truncated, not rounded.
• The result’s data type comes from the whole list, so mixed types can fail on conversion.
• A non-deterministic index (NEWID, RAND) is worked out once per WHEN and can return NULL. Store it first.

Microsoft’s reference page for CHOOSE (Transact-SQL) covers the syntax and its return-type rules. For more T-SQL notes, see my SQL Server DBA handbook.

This topic came up in the SQLServerCentral Question of the Day: Using CHOOSE().

Clive Roberts DBA