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

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 http://www.dotnetfunda.com/articles/show/1993/let39s-learn-choose-function-in-sql-server-2012

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
DECLARE @T TABLE(SomeText VARCHAR(20),Amount INT)

--Populate the table variable with some data
INSERT INTO @T VALUES
('Type 1',100),
('Type 2',150),
('Type 1',50),
('Type 2',75)

--Display the original record
SELECT *
FROM @t 

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

Solution

SELECT 	
	CHOOSE( CASE WHEN t.Amount >= 100 THEN 1 ELSE 2 END,  
			t.SomeText + ' : more than 100', 
			t.SomeText + ' : less than 100')  Mode,
	t.Amount
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.