Sunday, March 1, 2015

SQL Server 2012 - the CHOOSE() function

SQL Server 2012 introduced several new logical functions, and in this post I am going to review the CHOOSE function.  Per MSFT, this function 'returns the item at the specified index from a list of values in SQL Server'.  I think it is much like an array would use an index to collect the specified item.

This is a very basic example of the CHOOSE function:

     SELECT CHOOSE(2,'NYSE','CME','ICE','BTEC' ) [Exchange]
     GO

Your results:










The index is an integer expression representing an index into the given list of items. Important to know that the list index always starts at 1.  If you run this one, for example, it will return NULL:

     SELECT CHOOSE(0,'NYSE','CME','ICE','BTEC' ) [Exchange]

Similarly, this will return NULL because there are only four items in my list:

    SELECT CHOOSE(5,'NYSE','CME','ICE','BTEC' ) [Exchange]

Now this example is a little more complex.  I am using the CHOOSE() function with a SELECT from the Person.Person table in AdventureWorks2012. I use CHOOSE() to bring the First, Middle and Last names out, based on the given index. You can see I've also used ISNULL on the MiddleName, to avoid bringing NULL back for any Person entries without a middle name or initial.  

     SELECT 
            CHOOSE(1,FirstName,MiddleName,LastName) + ' ' +
            ISNULL(CHOOSE(2,FirstName,MiddleName,LastName), '') + ' ' +
            CHOOSE(3,FirstName,MiddleName,LastName) [Name]
     FROM
            Person.Person
     GROUP BY
            CHOOSE(1,FirstName,MiddleName,LastName) + ' ' +
            ISNULL(CHOOSE(2,FirstName,MiddleName,LastName), '') + ' ' +
            CHOOSE(3,FirstName,MiddleName,LastName
     ORDER BY
           CHOOSE(1,FirstName,MiddleName,LastName) + ' ' +
           ISNULL(CHOOSE(2,FirstName,MiddleName,LastName), '') + ' ' +
           CHOOSE(3,FirstName,MiddleName,LastName


And the results...


















Take a look at this from BOL for more information on the CHOOSE() function, and for a couple more use examples:
    https://msdn.microsoft.com/en-us/library/hh213019.aspx