In this blog we are going to talk about a little known T-SQL Command called CHOOSE. As its name suggests it allows some kind of selection to be made from a list. So the question is where and how will we use it. A common scenario where CHOOSE syntax would be helpful is when navigating the hierarchy. This might be very familiar for developers working with MS SQL Server Analysis Services NEXT MEMBER or PREVIOUS MEMBER MDX Queries. However the CHOOSE command implicitly doesn’t understand the hierarchy therefore it needs to be coded into the logic. The CHOOSE command isn’t really helpful in its current form because it doesn’t support a single column array index, e.g. if you’re looking for the third highest batter in a game you still need to use the rank function to get the correct result instead of simply choosing the third row from the set.
One Common way the CHOOSE command can be used is below
SELECT CHOOSE (1, ‘Jan’, ‘Feb’, ‘March’, ‘April’) AS Month;
RESULT is Jan
One weird way to use the CHOOSE command is as below
SELECT cHoose(4, [ClientName], [UserName], [ClientPh], [ClientAddress]) FROM client.account
Where the number 4 means it will return the data from the fourth column. But I can’t find any real world scenarios where something like this would be useful. Since it returns a single column array without any ID I doubt if we can use this result set for any Joins or drop down lists. TO summarize CHOOSE seems to be one of those features which wasn’t thought out. It provides feature that nobody can really find a use case for. But if you have a scenario you think will need this command please let me know I would love to hear the approach and the logic.
The CHOOSE command is collation sensitive so if there are two columns that do not have the same collation then the command fails , also the command only works for data that can be implicitly converted to varchar /nvarchar format.