SQL Server 2012: How to Use CHOOSE() Logical Function

Posted January 31, 2013 by Vishwanath Dalvi in Database

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.