CHOOSE Function – SQL Server Denali

SQL Server Denali comes with one more new logical function which helps to pull data at the specified index from a list of values. CHOOSE() is the function that’s been added. Again this function exists in Access , so if you migrate the db from Access there is no need to rewrite your query using CASE statement. In simple this function is a short form of CASE statement.

Syntax for this function is below

CHOOSE ( index, val_1, val_2 [, val_n ] )

Let’s populate some data to test this function

USE Tempdb
GO
CREATE TABLE tblchoose(product_id INT, stock bit)
INSERT tblchoose VALUES(1,0),(2,1),(3,1),(4,0),(5,1)

I’m going to choose on product_id column so that based on the input value it will return the product name

SELECT
CHOOSE(product_id,'Driller','Hammer','Axe','Shower Cap','Steel Plate')
AS 'Product Name'
,IIF(stock=1,'In Stock', 'Out of Stock') AS Stock
FROM tempdb..tblchoose

From the query above you can see that it’s returning the data from the specified index , in this case the index is the product name. For 1 it’s returning the value Driller for 3 it’s returning Axe like that based on the input value. If you have more values in index then your choose query will be looking big it’s better if you have lesser index value.