SQL Choose Function

choose function

The SQL Choose() function was initially introduced to SQL Server 2012.

This function migrated from MS Access, and was primarily added to assist with migrations from Access to SQL Server.

With this in mind, our general recommendation is not to use it unless you have to.

The syntax for the CHOOSE function is

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

Take note of:

  • The index value is an integer based expression and if you provide it something other than an integer it will round it to the nearest integer.
  • If the index is out of range of the array the function will return null.

The CHOOSE() function enables you to return one value from a given array or list, as in the example below.

SQL CHOOSE Function From List Example

If you have 3 values, and you want the 3rd one selected you can do this.

-------------------------------------------------------
SELECT CHOOSE(3, 'Value1','Value2','Value3')
-------------------------------------------------------

Real World Case Of SQL CHOOSE Function Usage

There are a number of scenarios where the CHOOSE() function can be utilized.

For example if you had a simple need to select 1 of three options. In this example we will use medals at an awards ceremony.

Example of CHOOSE Function


-----------------------------------------------
DECLARE @placing SMALLINT

SET @placing = 2

SELECT CHOOSE(@placing, 'Gold','Silver','Bronze')

-----------------------------------------------

There are much more efficient ways to do this, so only use the SQL CHOOSE() function if you really need to.

Image credit

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.