CHOOSE() Function In SQL Server 2012 (Code Named Denali)

Choose is the new function introduced in SQL Server 2011. This function helps in finding value from a the comma separated list based on position.

You Might have come across scenario where you have used case statement of find value of based on some condition.

Support based in index value you have to find week day name then probably case statement is appropriate for it.

Declare @WeekIndex Tinyint
Set @WeekIndex=6
Select Case @WeekIndex 
When 1 then 'Monday'
When 2 then 'Tuesday'
When 3 then 'Wednesday'
When 4 then 'Thursday'
When 5 then 'Friday'
When 6 then 'Saturday'
When 7 then 'Sunday'
End As Value

Output :

Value
---------
Saturday

 

Lets Use similar thing with CHOOSE() Function.

Select CHOOSE(6,'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')  As Value

Output :

Value
------------
Saturday

 

Lets Look at following Query Which fetches employee records with status, I have used Case Statement and CHOOSE() function to find Status Of Employee.

Select *,
Case IsActive 
When 1 Then 'Working' 
When 2 Then 'Resigned' 
When 3 Then 'InActive' 
End As Status_With_Case,
CHOOSE(IsActive,'Working','Resigned','IsActive') As Status_With_Choose
from Records

Output :

So you can see ease of using Choose function, however things can be achieved by Case statement also, but the benefit is CHOOSE() is very simple to implement and reduces complexity.