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
--Display the original record
The output is :
SomeText Amount 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"
Type 1 100
Type 2 150
Type 1 50
Type 2 75
So let's see, how can we achieve thisSolution
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:
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.