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.
