Choose Function in TSQL - SQL Server Tutorial / TSQL Tutorial

Choose() Function was introduced in SQL Server 2012. The function returns you the value from list of values by using the index.

Select Choose(Index,Value1,Value2,Value3).

The Index starts with 1. The maximum values you can have it 254.The data type of returned value depend upon the index number you used to select the value.If you have values with different data types, Choose function will return the data type with highest precedence from the list of values.

Microsoft Link for Data Type Precedence
https://msdn.microsoft.com/en-us/library/ms190309.aspx

Let'g give a try
If the values will be same data type then that will be the data type of value return.

Select Choose(1,'Aamir','Shahzad')
--output will be Aamir

In below example ,we see that we have 2 that is integer and 'Aamir' is string. As we are selecting 2, we will be Ok as Integer has higher precedence than character data type.

Select Choose ( 1,2,'AAMIR')
--Output will be 2 

Now if we try to reverse the above example. 'Aamir' is character value and 2 is integer. The higher precedence is Integer and we are trying to choose 'Aamir'. Implicit conversion for Character to Integer is not possible so we got error.

Select Choose(1,'Aamir',2)
Msg 245, Level 16, State 1, Line 49
Conversion failed when converting the varchar value 'Aamir' to data type int.

There could be multiple scenarios where we will be able to use Choose function. Let's consider below requirement. I have sales table and there is sale date. If we want to generate a report and have the Quarter information such as Quarter1,Quarter2,Quarter3 and Quarter4, we can use Choose function to extract that value depending upon Quarter Number that can be returned by DatePart function.

--drop table #Sales 
Create table #Sales (
SalePersonID INT, SalePersonName Varchar(100),SaleAmt INT,SaleDate DaTE)
insert into #Sales values (1,'AAmir Shahzad',1000,'2015-04-09')
insert into #Sales values (2,'Julie Crystal',1000,'2015-01-11')
insert into #Sales values (3,'M Raza',1000,'2015-02-21')
insert into #Sales values (4,'John Smith',1000,'2015-12-09')
insert into #Sales values (5,'Sukhjeet Singh',1000,'2015-06-30')
insert into #Sales values (6,'Ricky Robert',1000,'2015-08-22')


Select 
SalePersonName,
SaleAmt,
DATEPART(QUARTER,SaleDate) AS Qtr#
,Choose(DATEPART(QUARTER,SaleDate),'Quarter1','Quarter2','Quarter3','Quarter4') 

AS QtrName from #Sales

Fig 1: Use Choose Function in TSQL to get Quarter Name from Quarter Number

Detail Demo Video on How Choose() Function works in SQL Server