How to dynamically set the values inside Choose statement of Sql Server 2012 (Denali)

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 331
Some times back I wrote an article on Choose Function of Sql Server 2012 (code name: Denali) in .net Funda .The article title is Let's learn Choose Function in Sql Server 2012 and can be accessed here

The general syntax of Choose Function is as under
CHOOSE ( index, val_1, val_2 [, val_n ] )

A careful observation will reveal that the first parameter is the index . Now suppose we need to set the index at run time by some expression.How can we do that?
In this code segment , with an example, we will address the problem.

Suppose we have the below data

--Create a table variable

--Populate the table variable with some data
('Type 1',100),
('Type 2',150),
('Type 1',50),
('Type 2',75)

--Display the original record

The output is :
SomeText	Amount
Type 1 100
Type 2 150
Type 1 50
Type 2 75

Our requirement is that if the Amount is greater than or equals to 100, then the some text field will be appended with "more than 100" else "less than 100"

So let's see, how can we achieve this


CHOOSE( CASE WHEN t.Amount >= 100 THEN 1 ELSE 2 END,
t.SomeText + ' : more than 100',
t.SomeText + ' : less than 100') Mode,
FROM @t t

We have set the expression at run time by using the CASE statement. So if the condition evaluates to true, then the index position will be 1 else 2. When the index position is 1, the value is appended with "more than 100" else when the value is 2 it is "less than 100"

The resultant output is:

Mode	               Amount
Type 1 : more than 100 100
Type 2 : more than 100 150
Type 1 : less than 100 50
Type 2 : less than 100 75

Hope this little tip will help.

Thanks for your time for reading.

Comments or Responses

Login to post response