Choose Function in SQL Server

Choose Function in SQL Server

Choose function pick the item located at specified index from the list of provided Values

Returns the data type with the highest precedence from the set of types passed to the function

Syntex:

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

Examples: in the item list, we have varchar values, in our scenario we have 6 items['IT','India','Dinesh','Babu','Verma','Computer']

1.       If we pass index= 2 it will show value located at position 2

Select Choose(2     ,'IT','India','Dinesh','Babu','Verma','Computer')

2.       If we pass index=5.2(numeric) it will first convert into integer(5) and will show value located at position 5.

Select Choose(5.2  ,'IT','India','Dinesh','Babu','Verma','Computer')

3.       If we pass -Ve value, it will return NULL

Select Choose(-3.3  ,'IT','India','Dinesh','Babu','Verma','Computer')

4.       If we pass out of index value >6 then it will return NULL

Select Choose(9    ,'IT','India','Dinesh','Babu','Verma','Computer')

Scenario: in the list of provided items we should have same data type values Else it show error

Example:

Select Choose(3  ,'DB',5 ,'IT','India','Dinesh','Babu','Verma','Computer')

If we execute above statement it will show below error.

Msg 245, Level 16, State 1, Line 24

Conversion failed when converting the varchar value 'IT' to data type int.

Please try above in Dev Environment.