SQL Server 2012: How to Use CHOOSE() Logical Function
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: 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.
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.
Follow the reactions below and share your own thoughts.