SQL Choose() Function in SQL Server 2012
SQL Choose() function introduced with SQL Server 2012 returns an input argument from a list of input values at the desired index which is specified as an input arguement as well
CHOOSE (index, arg1, arg2 [, argN])
The first input argument to the SQL Choose() function is "index" and it is in integer data type. The input arguments can be thought as an array with index starting from 1.
SQL CHOOSE() Function Samples
In this T-SQL tutorial, SQL programmers can find Choose() function sample codes which can help to understand where and how to use SQL Choose() function.
Choose() Function and Index Argument
The input index parameter or index argument to Choose() sql function can be a static integer value, or can be an integer variable parameter.
DECLARE @index int = 2
SELECT
Choose(1, 'First','Second','Third','Fourth','Fifth') [Static Index],
Choose(@index, 'First','Second','Third','Fourth','Fifth') [Variable Index]
Choose() Function with Invalid Index
Of course it is possible to set the 1-based index value to zero (0) or to an index that is out of the range of the input array. In these cases the SQL Server Choose() function returns NULL as return value.
SELECT
CHOOSE(0, 'T-SQL', 'PL/SQL', 'OSQL'),
CHOOSE(999, 'A', 'B', 'C')
The index argument of the Choose() function can be NULL instead of a valid integer value. In such cases again, SQL Server function Choose() will return NULL value as output.
declare @i int = NULL
SELECT CHOOSE(@i, 'CHOOSE()', 'CONCAT()', 'IIF()')
SELECT CHOOSE(NULL, 'FIRST_VALUE()_', 'LAST_VALUE()', 'LEAD()', 'LAG()')
Choose() Function as an Expression to Concat() Function
Here is an other t-sql sample code where SQL Choose() function and Concat() function are used together in single SELECT statement. Note that the SQL Server Choose() function is used as an input expression argument to Concat() string concatenation function.
DECLARE @months int = 1
SELECT
CONCAT('Last day of ', @months, ' months later is ',
Choose(
@months,
eomonth(getdate(),1),
eomonth(getdate(),2),
eomonth(getdate(),3),
eomonth(getdate(),4),
eomonth(getdate(),5),
eomonth(getdate(),6),
eomonth(getdate(),7),
eomonth(getdate(),8),
eomonth(getdate(),9),
eomonth(getdate(),10),
eomonth(getdate(),11),
eomonth(getdate(),12)
)
)
I hope T-SQL developers are ready to use SQL CHOOSE() function in their codes. If you wonder other T-SQL functions introduced with SQL Server 2012, you can find a list of functions here New T-SQL Functions in SQL Server 2012 and here SQL Analytic Functions in SQL Server 2012