Let's learn Choose Function in Sql Server 2012

Niladri.Biswas
Posted by in Sql Server category on for Beginner level | Points: 100 | Views : 4121 red flag

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)