CHOOSE ( Transact-SQL)

 

Brief Description:      Returns the item at the specified index from a list of values

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

Availability:               All Versions of SQL Server 2012

Details:                       CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned

 

Examples:

1.        

declare @a int = 1

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

 

output

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

selectedmonth

Jan

 

set @a = 6

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

output

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

selectedmonth

Jun

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

2.

 

Following settings will all return null as output for out of bound index values

 

declare @a int

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

declare @a int = 0

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

declare @a int = 13

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

declare @a int = -1

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

 

 

output

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

selectedmonth

NULL

 

 

 

 

Pros:  

 

·         Short hand expression for array of values

·         Conversion from Access to SQL Server is straight forward 

 

Cons:  

 

·         Index value NULL need to be handled

·         Out of bound values return NULL