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
 


1 comment:

  1. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete