SQL CHOOSE

SQL Server has a list of logical functions that can be used on data.  The first of these logical functions is the CHOOSE() function.  This function will return the value identified at the specified index.  Here is what the syntax for the CHOOSE() function looks like:

CHOOSE(index value, value 1, value 2, value 3, etc.)

A pretty simple example of the CHOOSE() function looks like this:

SELECT CHOOSE(3, 'Executive', 'Senior Management', 'Vice President')

The above would return 'Vice President', since the index for lookup is 3 and the third value is 'Vice President'.

So how would a function like this be useful?  Well, we could use it to list out the months of the year for an employee's birthday, like so:

SELECT CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
FROM Employee

How does this differ from what you might see using the CASE() function?  There isn't a difference.  CHOOSE() is just a short hand version of CASE().  It definitely looks neater than CASE(), since you don't have to do so much coding.  Using the example above, CASE() would look something like this:

SELECT CASE Month(Birthday)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
FROM Employee

As you can see, much mode coding needs to be done, while also adding the value you are looking for.  CHOOSE() already knows the value you are looking for and selects it based on the order in the provided value list.  Any value searched for that is not contained in the value list (such as 13 for our months) will return a NULL value.

How do you think you could use the CHOOSE() function at your organization?

To read up more on SQL CHOOSE(), go here: SQL CHOOSE