SQL Server 2012 introduced the powerful new CHOOSE() function. This tech-recipe will explore its use and common mistakes through some simple examples.

The CHOOSE() function returns the item at a specified index. The behavior is the same as the list of items in array and uses array index to get the item at specified index.

Syntax

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

 
• Index argument accepts integer expression, and it has to start with 1-based index.
• Val_1 to val_n list of items.

Examples

Example 1.1 – CHOOSE() with index

SELECT CHOOSE(0 ,'tech', 'recipes', 'cookbook' ) AS 'index_as_0'
GO

index_as_0
----------
NULL

 
This shows NULL because CHOOSE() accepts index arguments and must start with one or a value greater than one.

Example 1.2 – CHOOSE() with valid index value

SELECT CHOOSE( 1 ,'tech' ,'recipes' ,'cookbook' ) AS 'index_as_1'
GO

index_as_1
----------
tech

 
This returns the ‘tech’ item as result from the value list because we have passed one as index argument.

Example 1.3 – CHOOSE() with index greater than number of items in the list

SELECT CHOOSE(4,'tech', 'recipes', 'cookbook') AS 'index_as_4'
GO

index_as_4
----------
NULL

 
This returns NULL because we are passing index argument as four, and we do not have any item at the fourth position.

Example 1.4 – CHOOSE() with decimal index

SELECT CHOOSE(2.9,  'tech',  'recipes',  'cookbook') AS 'decimal_index'
GO

decimal_index
-------------
recipes

 
This does not return any error. It shows ‘recipes’ in result. When you are passing the first argument as 2.9, it converts a decimal value into an integer, treats the decimal value 2.9 as 2, and shows the ‘recipes’ item as the result.

Example 1.5 CHOOSE() with negative index

SELECT CHOOSE(-2, 'tech', 'recipes', 'cookbook') AS 'Negative_index'
GO

Negative_index
--------------
NULL

 
This results in NULL because you are passing the first argument as a negative value which violates the syntax. Always make sure you always pass the positive integer value.

Example 1.6 CHOOSE() with variable

DECLARE @index AS INT = 3
SELECT CHOOSE(@index, 'tech', 'recipes', 'cookbook') AS 'Index_as_Variable'
GO

Index_as_Variable
-----------------
cookbook

 
We have declared an int variable @index with value three and have passed the variable as index value. It is showing ‘cookbook’ because it is the third index item in the list.