CHOOSE function

The non-standard CHOOSE function was introduced in SQL Server 2012.

The CHOOSE function is used to select one of several options. The selection is made based on the index (option number), which is the first parameter of the function. The remaining parameters are options. The option whose number in the parameter list matches the index will be selected.

For example, the query

select CHOOSE(2,'PC','Laptop','Printer');

returns Laptop, so this is second item in the list.

Of greater interest is the case where the index is an expression that includes table columns.

Let’s say we need to display the name of the point instead of the point number (in the “Recycled Materials” database.

Since the point names are not stored in the database, we will generate them “on the fly” using the CHOOSE function:

select distinct point,
       CHOOSE(point, 'point A', 'point B', 'point C') point_name
from outcome;

Obviously, the CHOOSE function is another special version of the CASE expression. Our example using the CASE function can be rewritten as:

select distinct point,
case point
    when 1 then 'point A'
    when 2 then 'point B'
    when 3 then 'point C'
end point_name
from outcome;

Если для указанного индекса совпадение не найдено, функция CHOOSE вернет NULL:

select CHOOSE(4,'PC','Laptop','Printer');

To handle this case, the CASE expression has an ELSE clause. Using this clause, we can output, for example, UNKNOWN instead of NULL (the default):

select case 4
    when 1 then 'PC'
    when 2 then 'Laptop'
    when 3 then 'Printer'
    else 'UNKNOWN'
end;

We can easily overcome this “drawback” of the CHOOSE function by wrapping it with the COALESCE function:

select COALESCE(CHOOSE(4,'PC','Laptop','Printer'),'UNKNOWN');

but this is already like “butter oil” - case from case.

We can say that the CHOOSE function has a specific application, but in these limited cases it allows to simplify the writing.

In conclusion, we will give one more example.

select model,
       CHOOSE(model%2+1, 'EVEN', 'ODD')
from product
where ISNUMERIC(model) =1;

The predicate in the WHERE clause is used to limit the models to those whose numbers are numeric. Note the data type of the model column! I hope that those who solve the exercises on the sql-ex.ru website are already aware of this. :-) They should also be aware that using the ISNUMERIC function in this context is not a radical solution.