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’.
DECLARE @i AS INT = 3;
SELECT CHOOSE(@i, 'One', 'Two', 'Three', 'Four', 'Five');
SET @i = 5;
SELECT CHOOSE(@i, 'One', 'Two', 'Three', 'Four', 'Five');
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
) ON [PRIMARY]
GO
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);
GO
Now create a SELECT statement against the table with CHOOSE and use the column for index.
SELECT
index_val, CHOOSE(index_val, 'One', 'Two', 'Three', 'Four', 'Five')
FROM MTB_Table_1;
GO
/* Result */
index_val
----------- -----
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.
Reference
- More about CHOOSE function at Microsoft Docs.
Related Article
- Read about the difference between IIF and CASE in SQL Server.