New Choose Function in SQL Server 2012

In this article I have described about new choose function in SQL Server 2012.
  • 4992

Choose() Function

This function is used to return the value out of a list based on its index number. You can think it as like an array. The Index number here starts from 1.


CHOOSE ( index, value1, value2.... [, valueN ] )

CHOOSE() Function excepts two parameters,

Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1. 

Value: List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1


SET @ShowIndex =5;

Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult 


In the preceding example we take index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.



2.  When passed a set of types to the function it returns the data type with the highest precedence; see:


SET @ShowIndex =5;

Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.


3. If an index value exceeds the bound of the array it returns NULL


SET @ShowIndex =9;

Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.




More Articles

© 2013 dotNetheaven. All rights reserved.