New features in SQL Server 2012

CHOOSE () : Returns the item at the specified index from the list of values

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

Example:

SELECT DATENAME(MM,GETDATE()) MONTH_NM,CHOOSE(DATEPART(Q,GETDATE()),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

------------------------------ -------

February                       1st Qtr

SELECT DATENAME(MM,DATEADD(M,2,GETDATE())) MONTH_NM,CHOOSE(DATEPART(Q,DATEADD(M,2,GETDATE())),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

------------------------------ -------

April                          2nd Qtr

SELECT DATENAME(MM,DATEADD(M,5,GETDATE())) MONTH_NM,CHOOSE(DATEPART(Q,DATEADD(M,5,GETDATE())),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

------------------------------ -------

July                           3rd Qtr

SELECT DATENAME(MM,DATEADD(M,8,GETDATE())) MONTH_NM,CHOOSE(DATEPART(Q,DATEADD(M,8,GETDATE())),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

------------------------------ -------

October                        4th Qtr

       Note: What happens if the index is out of range of specified list of values? Try this:

SELECT CHOOSE(5,'First','Second') as OUT_OF_RANGE

Output:

OUT_OF_RANGE

------------

NULL

 It returns NULL

<<IIF()                                                    Go to Main Page