The Choose Function in sql server

The CHOOSE function is also a logical operator, but it behaves in a somewhat different manner. CHOOSE is similar to an index in an array, assuming the array is a list of arguments. The syntax for the CHOOSE function is as follows:

CHOOSE (index, val_1, val_2 [, val_n])

Index is a 1-based integer that acts as the index into the list of values. The corresponding values are the list of values that will be searched. The following script illustrates the use of the function:

See the example

SELECT CHOOSE (3, 'Lions', 'Tigers', 'Bears') Result

See the output

If the specified index value has a numeric data type other than integer, then the numeric value will be implicitly converted to integer type. For example, 3.68 will be converted to 3. See the example.

If there are no items in the specified index, or index out of range, then CHOOSE function will return NULL value. If we use index as 5 in given example it will return Null value. See the example.

If we use string as index value it will throw error. See the example