CHOOSE Function in SQL Server

In SQL Server, the CHOOSE function returns the item at the provided index from a list of values. CHOOSE is an index into an array, with the array being made up of the arguments that come after the index argument. The index argument determines which of the values will be returned.

The value is implicitly transformed to an integer if the specified index value has a numeric data type other than int. If the index value is greater than the array’s boundaries, CHOOSE returns null.

Examples:
SELECT CHOOSE ( 1, ‘Scott’, ‘Wills’, ‘Miller’, ‘Mia’ ) AS Result;
Result:
Scott.

Let’s continue with a useful dataset to better understand the function’s behavior.

–Setting up the data-set.
CREATE TABLE tbStudentDetails(
StudentID INT,
StudentName VARCHAR(30),
GradeID INT);

INSERT INTO tbStudentDetails VALUES
(1001,’Zafar Iqbal’,2),
(1002,’Tahir Iqbal’,1),
(1003,’Danial Hussain’,3)

SELECT * FROM tbStudentDetails
The result is-
StudentID   StudentName   GradeID
1001            Zafar Iqbal         2
1002            Tahir Iqbal         1
1003            Danial Hussain  3

Now, lets use “CHOOSE” function to convert the “GradeID” into actual grades.

SELECT StudentID,
              StudentName,
              GradeID,
              CHOOSE(GradeID,’A+’,’A’,’B’)
FROM tbStudentDetails

Result:
StudentID      StudentName   GradeID   Grade
1001                Zafar Iqbal             2              A
1002                Tahir Iqbal             1              A+
1003                Danial Hussain      3              B

It considers the lowest number first, and assigns the first input value.

Hope you find this article helpful.