How to use CHOOSE in SQL Server

CHOOSE is a Transact-SQL function used to return a single value from a list based on the specific index. Here is the syntax:

CHOOSE ( index, value_1, value_2 [, value_n ] )  

Here, index is an integer which specifies the index of the value to be returned. Index starts from 1.

value_1, value_2 [, value_n ] is the list of values of any data type. These values are separated by comma.

let us see couple of examples on how to use it.

Simple example of using CHOOSE with a variable as index

In this example, let us pass a variable of integer data type as an index value and the list of values in CHOOSE be from ‘One’ … ‘Five’.


SELECT CHOOSE(@i, 'One', 'Two', 'Three', 'Four', 'Five');

SET @i = 5;

SELECT CHOOSE(@i, 'One', 'Two', 'Three', 'Four', 'Five');
How to use CHOOSE in SQL Server

Example with a table column as index

Let us see another example by fetching the index value from an integer column of a table.

To start with, let us create a single column table and insert some index values starting from 1.

CREATE TABLE [dbo].[MTB_Table_1](
	[index_val] [int] NOT NULL

INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (1);
INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (2);
INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (3);
INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (4);
INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (5);
INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (6);
INSERT INTO [dbo].[MTB_Table_1] ([index_val]) VALUES (7);

Now create a SELECT statement against the table with CHOOSE and use the column for index.

    index_val, CHOOSE(index_val, 'One', 'Two', 'Three', 'Four', 'Five') 
    FROM MTB_Table_1;

/* Result */

----------- -----
1           One
2           Two
3           Three
4           Four
5           Five
6           NULL
7           NULL

(7 rows affected)

If you notice the results, you can see the CHOOSE function is returning the value from the list specific to the index from the column. When the index is out of bounds of the list of values, it will return NULL.


Related Article

Leave your thoughts...

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