In this article we will learn Choose Function in Sql Server 2012
Introduction
Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into Choose Function.
Purpose
Given a list of values and a position, this function will return the value at the indicated position.
Syntax
Format (expression, format [, culture])
Where,
Position = The position number of the value to return. Position number starts from 1
Value1..ValueN => List of values.
Example 1: Correct Position Number
Select Choose (1,'Simple Choose Demo', 'This is an example') As [Choose Demo]
/*
Choose Demo
------------
Simple Choose Demo
*/
In this example, we have specified the position as 1 and hence out of the two values, the first appears as the result.
Example 2: Position Number < 1
Select Choose (0,'Simple Choose Demo', 'This is an example') As [Choose Demo]
--OR
Select Choose (-1,'Simple Choose Demo', 'This is an example') As [Choose Demo]
/*
Choose Demo
------------
NULL
*/
If the position is less than 1 then the result will be null.
Example 3: Position Number > Number of values
Select Choose (3,'Simple Choose Demo', 'This is an example') As [Choose Demo]
/*
Choose Demo
------------
NULL
*/
If the position is more than the number of values then the result will be null.
Example 4: Fractional Position Number
Select Choose (2.1,'Simple Choose Demo', 'This is an example') As [Choose Demo]
--OR
Select Choose (2.99,'Simple Choose Demo', 'This is an example') As [Choose Demo]
--OR
Select Choose (.5/.25,'Simple Choose Demo', 'This is an example') As [Choose Demo]
/*
Choose Demo
------------
This is an example
*/
In this case, it gets rounded off to the whole number.
Example 5: A real time example
Suppose we are preparing a quiz game where every question has four options(Kindly ignore the designing of the table.Focus on the usage of Choose function). Now if we need to find out which one the user has answered, we can use this function to get the answer like the below
Declare @tblQuiz table
(
[QuestionID] int identity
,[UserName] Varchar(100)
,[Question] Varchar(100)
,[AnswerID] int
,[Option1] varchar(50)
,[Option2] varchar(50)
,[Option3] varchar(50)
,[Option4] varchar(50)
)
Insert into @tblQuiz Values
('Deepak','Who was the Indian Skipper when India bagged the Second World Cup Cricket Tournament?',2,'K.Dev','M.S.Dhoni','M.Singh','S.Gavaskar')
,('Deepak','Which nation got the Cricket World Cup 2011?',4,'Srilanka','England','Bangladesh','India')
,('Deepak','Who was the Man of the Tournament in Cricket World Cup 2011?',4,'R.Singh','S.Tendulkar','Y.Singh','Z.Khan')
,('Deepak','Between which two nation the Cricket World Cup 2011 final match was played?',1,'India and Australia','India and Srilanka','WestIndies and Pakistan','Srilanka and South Africa')
Select * from @tblQuiz
/* Results truncated for spacing
QuestionID UserName Question AnsID Opt1 Opt2 Opt3 Opt4
-------- -------- ---- --- ---- --- -- ----
1 Deepak Who ... 2 K.Dev M.S.Dho M.Sin S.Gav
2 Deepak Which... 4 Sri Eng Bang India
3 Deepak Who ... 4 R.Singh S.Ten Y.Singh Z.Khan
4 Deepak Bet ... 1 Ind&Aus Ind&Sri WI&Pak Sri&SA
*/
Select
[UserName]
,[Question]
,[Answer Given] = Choose([AnswerID],[Option1],[Option2],[Option3],[Option4])
from @tblQuiz
/* Output (Results truncated for spacing)
UserName Question Answer Given
-------- -------- ------------
Deepak Who was the.. M.S.Dhoni
Deepak Which nation.. India
Deepak Who was the Man.. Z.Khan
Deepak Between which.. India and Australia
*/
So depending on the [AnswerID] column, we can easily find out the answer given by the user.
How SQL Server Engine view the Choose Function?
The execution plan being generated is as under
As can be figured out that Choose gets expanded into the corresponding Case statement and that's how the operation happens
An equivalent case statement will give the same execution plan
SELECT
[UserName]
,[Question]
,[Answer Given] = CASE WHEN [AnswerID] = 1 THEN [Option1]
WHEN [AnswerID] = 2 THEN [Option2]
WHEN [AnswerID] = 3 THEN [Option3]
WHEN [AnswerID] = 4 THEN [Option4]
END
from @tblQuiz
Conclusion
SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Choose function and it's usefulness into the T-Sql programming parlance.Hope the article will be useful.