What is CHOOSE?
CHOOSE is certainly one of the less known functions in SQL Server, but this function is wrongly considered a shadowy existence, because it can make many annoying queries much easier. You can often use
CHOOSE to simplify long, complex
CASE WHEN statements, namely whenever you need to select an element from a list on the basis of a number, i.e. like an array access via an index.
How can you work with CHOOSE?
The basic functionality of
CHOOSE is quite simple: you pass a number (the index you want, which is 1-based here) as the first argument to the
CHOOSE function, and then a list of return values.
CHOOSE returns the corresponding value from the list. Let’s look at an example of this, let’s create a table for testing:
DROP TABLE IF EXISTS #Ninjas CREATE TABLE #Ninjas ( NinjaName varchar(250) ,StyleID int )
Let us now add some values to the table:
INSERT INTO #Ninjas (NinjaName, StyleID) VALUES ('Deadflow', 1) ,('Jadestroke', 3) ,('Phantom Flow', 2) ,('Death Flash', 3)
If, without having a table that resolves the styles, we want to resolve the
StyleID to a readable name, we can do so as follows
SELECT NinjaName ,StyleID ,CASE StyleID WHEN 1 THEN 'Katana' WHEN 2 THEN 'Bajutsu' WHEN 3 THEN 'Bojutsu' WHEN 4 THEN 'Shuriken' WHEN 5 THEN 'Assassin' END FROM #Ninjas
However, this syntax is not very clear and can be abbreviated with
CHOOSE. The same query would then look like the following:
SELECT NinjaName ,StyleID ,CHOOSE(StyleID, 'Katana', 'Bajutsu', 'Bojutsu', 'Shuriken', 'Assassin') FROM #Ninjas
Similarly, you can work with date columns for example, but you can run the following query on the WideWorldImporters database, which resolves the month names
SELECT OrderID ,OrderDate ,CHOOSE(MONTH(OrderDate) ,'Jan' ,'Feb' ,'Mar' ,'Apr' ,'May' ,'Jun' ,'Jul' ,'Aug' ,'Sep' ,'Oct' ,'Nov' ,'Dec') as OrderMonth FROM Sales.Orders
And this also works with other numerical values, for example we try to classify how long a customer has been a customer in the WideWorldImporters database:
SELECT CustomerID ,AccountOpenedDate ,CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') FROM Sales.Customers
If you run this query, you will see that there are some customers who have been customers since 2013 and are therefore leaving the index. For these customers we now have to find a further classification. Fortunately, for a value that does not match any index (for example, a value less than one or greater than the number of elements in the array),
CHOOSE returns a
NULL value. Of course, we can now handle this accordingly:
SELECT CustomerID ,AccountOpenedDate ,ISNULL(CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') ,'Jurassic') FROM Sales.Customers
Unfortunately, we still see here that the return value for the oldest customers is only “Jurassi”, the “c” is truncated, because
CHOOSE chooses the smallest possible data type as return value, to which
ISNULL sticks and truncates the return value accordingly. This is nicer if you use
SELECT CustomerID ,AccountOpenedDate ,COALESCE(CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') ,'Jurassic') FROM Sales.Customers
Now the Dinosaurs among your customers are marked accordingly, and you can continue editing.