Choose function in SQL Server

Leave a Comment

we will discuss Choose function in SQL Server

Choose function 
  • Introduced in SQL Server 2012
  • Returns the item at the specified index from the list of available values
  • The index position starts at 1 and NOT 0 (ZERO)
Syntax : CHOOSE( index, val_1, val_2, ... )

Example : Returns the item at index position 2

SELECT CHOOSE(2, 'India','US', 'UK') AS Country

Output : 
Choose function in SQL Server

Example : Using CHOOSE() function with table data. We will use the following Employees table for this example.
sql server 2012 choose function

SQL Script to create Employees table
Create table Employees
     Id int primary key identity,
     Name nvarchar(10),
     DateOfBirth date

Insert into Employees values ('Mark', '01/11/1980')
Insert into Employees values ('John', '12/12/1981')
Insert into Employees values ('Amy', '11/21/1979')
Insert into Employees values ('Ben', '05/14/1978')
Insert into Employees values ('Sara', '03/17/1970')
Insert into Employees values ('David', '04/05/1978')

We want to display Month name along with employee Name and Date of Birth.
choose function in sql 2012

Using CASE statement in SQL Server

SELECT Name, DateOfBirth,
        CASE DATEPART(MM, DateOfBirth)
            WHEN 1 THEN 'JAN'
            WHEN 2 THEN 'FEB'
            WHEN 3 THEN 'MAR'
            WHEN 4 THEN 'APR'
            WHEN 5 THEN 'MAY'
            WHEN 6 THEN 'JUN'
            WHEN 7 THEN 'JUL'
            WHEN 8 THEN 'AUG'
            WHEN 9 THEN 'SEP'
            WHEN 10 THEN 'OCT'
            WHEN 11 THEN 'NOV'
            WHEN 12 THEN 'DEC'
       AS [MONTH]
FROM Employees

Using CHOOSE function in SQL Server : The amount of code we have to write is lot less than using CASE statement.

SELECT Name, DateOfBirth,CHOOSE(DATEPART(MM, DateOfBirth),
       'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG',
       'SEP', 'OCT', 'NOV', 'DEC') AS [MONTH]
FROM Employees


Post a Comment

Note: only a member of this blog may post a comment.