CHOOSE is one of the new built-in logical function introduced as a Part of Sql Server 2012. It returns the value at the specified index position from the list of values.

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

Example 1: CHOOSE Basic Example

SELECT CHOOSE(1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(2,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(4,'Spring','Summer','Autumn','Winter')

Result:

Example 2: CHOOSE function with variables as index and values.

DECLARE @SeasonId INT = 2, @Season1 Varchar(10) = 'Spring',
 @Season2 Varchar(10) = 'Summer',@Season3 Varchar(10) = 'Autumn',
 @Season4 Varchar(10) = 'Winter'

SELECT CHOOSE(@SeasonId, @Season1, @Season2, @Season3, @Season4)

Result: Summer

Example 3: CHOOSE will return NULL if INDEX position is outside the range of values.

SELECT CHOOSE(-10,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(0,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(5,'Spring','Summer','Autumn','Winter')

Result:

Example 4: If index value is numeric, it will be implicitly converted to INT.

SELECT CHOOSE(3.1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3.85,'Spring','Summer','Autumn','Winter')

Result:

Example 5: CHOOSE function Return Datatype will be the one with highest precedence from the Datatypes of the list of values

SELECT CHOOSE(3, 40.58, 50, 60, 70)

Result: 60.00

Example 6: CHOOSE function with Index Type as Varchar

SELECT CHOOSE('TEST','Spring','Summer','Autumn','Winter')

Result:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘TEST’ to data type int.

SELECT CHOOSE('3','Spring','Summer','Autumn','Winter')

Result: Autumn

You may like to read the below new built-in function’s introduced in Sql Server 2012:

Please correct me, if my understanding is wrong. Comments are always welcome.