|
|
SQL Server 2012 introduces a new category of built-in scalar functions, the logical functions, which as the name implies, performs logical operations. SQL Server 2012 introduces 2 new scalar functions under this newly created logical function category, namely the CHOOSE function and the IIF function. The CHOOSE logical function returns the item at the specified index from a list of values while the IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false.
|
CHOOSE Logical Function
The CHOOSE logical function acts like an index into an array, where the array is composed of the parameters that follow the index parameter. The index parameter determines which of the following values will be returned. Here’s the syntax of the CHOOSE logical function:
CHOOSE ( <index>, <value_1>, <value_2> [, <value_n>] )
The <index> parameter is an integer expression that represents a 1-based index into the list of items following it. If the provided index value has a numeric data type other than INT, then the value is implicitly converted to an INT data type. If the index value is 0, a negative number or exceeds the bounds of the array of values, then the CHOOSE logical function will return a NULL value.
The <value_1>, <value_2>, <value_n> parameters are a list of values of any data type. From these parameters, which may be of different data types, the CHOOSE function will return the data type with the highest precedence.
Here’s a couple of examples on how the CHOOSE logical function can be used.
SELECT [ContestantName], CHOOSE([Position], '1st Place', '2nd Place', '3rd Place') AS [Placement]
FROM [dbo].[Contestant]
SELECT [PlayerName], CHOOSE([Ranking], 'Gold Medal', 'Silver Medal', 'Bronze Medal') AS [Reward]
FROM [dbo].[Player]
As another example, here’s how to translate a numeric month into the name of the month using the CHOOSE logical function. This same statement can be done using the CASE statement. This can be useful when only the month and year is stored in the database instead of the full date, such as the case of credit card expiration dates:
SELECT [CardHolderName], CHOOSE([ExpirationMonth], 'January', 'February', 'March', 'April',
'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') + ' ' +
CAST([ExpirationYear] AS VARCHAR(4)) AS [ExpirationDate]
FROM [dbo].[CreditCard]
IIF Logical Function
The IIF logical function returns one of two values, depending on whether the first parameter of the function, which is a Boolean expression, evaluates to true or false. The syntax for the IIF logical function is as follows:
IIF ( <boolean_expression>, <true_value>, <false_value> )
The <boolean_expression> parameter is any valid Boolean expression. If this parameter is not a Boolean expression, then a syntax error will be raised. The <true_value> parameter is the value that the IIF logical function will return if the <Boolean_expression> evaluates to true. On the other hand, the <false_value> parameter is the value that the IIF logical function will return if the <Boolean_expression> evaluates to false or unknown. The data type of the returned value will be the data type with the highest precedence between the data types in the <true_value> parameter and <false_value> parameter.
Here’s a simple example of the use of the IIF logical function:
SELECT [StudentNumber], IIF( [TestScore] >= 65, 'Pass', 'Fail' ) AS [PassFail]
FROM [dbo].[StudentTest]
Here’s another example but using a nested IIF function:
SELECT [StudentNumber], IIF( [GradePoint] BETWEEN 92 AND 100, 'A',
IIF( [GradePoint] BETWEEN 83 AND 91, 'B',
IIF( [GradePoint] BETWEEN 74 AND 82, 'C',
IIF( [GradePoint] BETWEEN 65 AND 73, 'D',
IIF( [GradePoint] < 65, 'F', 'I' ))))) AS [LetterGrade]
FROM [dbo].[Student]
Here’s how the same query will look like using the CASE statement instead of the IIF function:
SELECT [StudentNumber], CASE WHEN [GradePoint] BETWEEN 92 AND 100 THEN 'A'
WHEN [GradePoint] BETWEEN 83 AND 91 THEN 'B'
WHEN [GradePoint] BETWEEN 74 AND 82 THEN 'C'
WHEN [GradePoint] BETWEEN 65 AND 73 THEN 'D'
WHEN [GradePoint] < 65 THEN 'F'
ELSE 'I' END AS [LetterGrade]
FROM [dbo].[Student]
|