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
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 AamirIn 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