TexCopy Sql Server 2005

Today I am starting my series on ‘What’s new in SQL 2012’ ; One of the interesting functions available is ‘CHOOSE’ which can be used to achieve some of the features of ‘CASE’ statement

Syntax

---

CHOOSE ( index, val_1, val_2 [, val_n ] )

Arguments

---

index

Is an integer expression that represents a 1-based index into the list of the items following it.

If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

val_1 … val_n

List of comma separated values of any data type.

Return Types

---

Returns the data type with the highest precedence from the set of types passed to the function. 

 

CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

 

The following example returns the third item from the list of values that is provided.

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

Here is the result set.

Result
-------------
Developer

DECLARE @Projects TABLE(I INT IDENTITY,ProjectName VARCHAR(100),ProjectState INT);
INSERT INTO @Projects(ProjectName,ProjectState) VALUES ('PR#1',1), ('PR#2',2), ('PR#3',2), ('PR#4',3),('PR#5',3);
--  non sql 2012 code
SELECT  * ,
        CASE (ProjectState)
          WHEN 1 THEN 'Assigned'
          WHEN 2 THEN 'In Progress'
          WHEN 3 THEN 'Closed'
        END AS ProjectState_With_Case
FROM    @Projects;

-- SQL 2012 Code 
SELECT  * , CHOOSE(ProjectState, 'Assigned', 'In Progress', 'Closed') AS ProjectState_With_Choose
FROM    @Projects;