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

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.

4 Responses to “SQL Server 2012: How to Use CHOOSE() Logical Function”

  1. June 19, 2013 at 7:14 am, Toni Marie said:

    It would also be helpful to show how CHOOSE() can be used in a productive manner aside from the mechanics of the statement. Some examples are shown in http://msdn.microsoft.com/en-us/library/hh213019.aspx, one of which is using it to select a season (I changed it to select a quarter of the year which gives a more precise result):

    USE AdventureWorks2012;
    GO
    SELECT JobTitle, HireDate, CHOOSE(MONTH(HireDate),’First’,'First’, ‘First’,'Second’,'Second’,'Second’,'Third’,
    ‘Third’,'Third’,'Fourth’,'Fourth’,'Fourth’) AS Quarter_Hired
    FROM HumanResources.Employee
    WHERE YEAR(HireDate) > 2005
    ORDER BY YEAR(HireDate);

    Here is the result set.
    JobTitle HireDate Quarter_Hired
    ————————————————– ———- ————-
    Sales Representative 2006-11-01 Fourth
    European Sales Manager 2006-05-18 Second
    Sales Representative 2006-07-01 Third
    Sales Representative 2006-07-01 Third
    Sales Representative 2007-07-01 Third
    Pacific Sales Manager 2007-04-15 Second
    Sales Representative 2007-07-01 Third

    Toni Marie

    Reply

    • June 19, 2013 at 7:59 am, Vishwanath Dalvi said:

      Thank you @Toni for useful example.

      I wrote this article considering introduction to choose function, will try to update it with more productive use.

      Reply

  2. June 19, 2013 at 8:28 am, Joe Celko said:

    I would never use this proprietary dialect. When I see people writing dialect, I know that the rest of their SQL will have design flaws based on their original programming language. This is a version of a spreadsheet function and the deprecated Oracle DECODE() function. The ANSI/ISO Standard SQL CASE expression can do the same things, it will port and can be optimized.

    Reply

    • June 19, 2013 at 3:15 pm, Ray said:

      CHOOSE() is unique from CASE in that you can use it like an array and unlike CASE, you do not need to know and code for all contingencies during development. Also, the majority of projects live a life-cycle void of contending with porting from one platform to another. If you’re designing an ERP or Data Warehouse, CHOOSE() may be a bad coding convention to adopt but for the rest… enjoy.

      Reply

Leave a Reply