Introduction to SQL Server’s Built-In Logical Functions

Logical functions provide a way to use logical conditions to display one of several values.  You can use logical functions to test a field’s value such as gender (M or F) and display another value(‘Male’ or ‘Female’) as a result.
In this article we describe how to use the CHOOSE and IIF functions.  CHOOSE is really a great way to pick one value from a list of indexed values, whereas IIF provides a compact means to provide the same type of conditional testing found with in the CASE statement.

If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

Introduction to SQL Server’s Built-In Logical Functions

The IIF and CHOOSE functions are new to SQL Server 2012.  They allow you to perform comparisons within the select statement to decide which of several values to return.

CHOOSE

The Choose function is used to select a value from a list.  It is really handy when you have a numeric value that represents a position, such as the numbers 1 through 12 represent the months of the year, and you want to use them to “map” to another value, such as seasons.

The format for chose is

CHOOSE(index, value list)

Given the index, which is a positive number, the CHOOSE function returns the corresponding entry from the value list.

For example

SELECT CHOOSE(2, 'A', 'B', 'C')

Returns B

You can use non integer values, such as FLOAT and DECIMAL, as well.  They are implicitly converted to their integer value.  Thus

SELECT CHOOSE(2.9, 'A', 'B', 'C')

Also returns B since 2.9, when converted to an integer, is 2.

Use CHOOSE to Map Date to Season of Year

Suppose the  Adventure Works hiring manager wants to know the season each hire occurs?  What can be done?  By using the number of the month, we can use the CHOOSE function to get the season.  Here is the CHOOSE function we can use:

CHOOSE(MONTH(HireDate),
       'Winter', 'Winter', 'Winter',
       'Spring', 'Spring', 'Spring',
       'Summer', 'Summer', 'Summer',
       'Fall', 'Fall', 'Fall')

MONTH, is a DateTime function, that returns a number 1-12 which corresponds to the month; January corresponds to 1 and December to 12.

If the HireDate is in October, MONTH is then 10, resulting in ‘Fall,’ the tenth element in the list, to be returned

Here is the SQL we can use to obtain the hiring manager’s request:

SELECT NationalIDNumber,
       JobTitle,
       MONTH(HireDate) as HireMonth,
       CHOOSE(MONTH(HireDate),
              'Winter', 'Winter', 'Winter',
              'Spring', 'Spring', 'Spring',
              'Summer', 'Summer', 'Summer',
              'Fall', 'Fall', 'Fall') as [Hiring Season]
FROM  HumanResources.Employee

One thing to point out is NULL is returned if the CHOOSE function index is less than one or greater than the number of items in the value list.

SELECT CHOOSE(0, 'A', 'B', 'C')

Returns NULL since the index is less than one, and

SELECT CHOOSE(4, 'A', 'B', 'C')

does as well, since the index value of 4 is greater than then number of items in the value list.

Here is another example.

Use Choose to Map Level to Title

Suppose the hiring manager wishes to each employee and a description of their level within the organization.  You’ll notice each employee is assigned a level

The organization levels start at the top with 0 and progress from there.  We can use the CHOOSE function to create a mapping between the organization level and description.  Since

the organization level starts with level starts with 0, we need to add 1; otherwise the first level (0) would always return NULL.  Remember, only index values greater than or equal to one are allowed.

After taking this into account the SQL we can use is:

SELECT NationalIDNumber,
       JobTitle,
       OrganizationLevel,
       CHOOSE(OrganizationLevel+1,
              'Executive', 'Executive',
              'Upper', 'Middle',
              'Lower', 'Lower') as [Organization Level Name]
FROM   HumanResources.Employee

Though our value lists have returned textual results, there is not reason it couldn’t be another datatype, such as a DATETIME or a numeric value.

The CHOOSE function is a really good fit when you which to select a value based on another.  I think if value list is too large though you may want to reconsider using another means to map.  For instance you could create a table to serve as a lookup.  Keep in mind that CHOOSE is “converted” to a CASE statement, so if you think a CASE statement “smells,” then surely CHOOSE will also stink!

Read More: How to use the Choose Function with Select >>

IIF Logical Function

The IIF statement is used to return one of two results based on the result of a Boolean condition.

Here is the general format of the statement

IIF ( Boolean expression, true value, false value )

IIF is shorthand for “inline if.”  If the Boolean expression is TRUE, then the true value is returned else the false value is returned.

The statement

SELECT IIF(10 > 100, '10 is weird', '10 is less than 100')

Returns ’10 is less than 100′ since the Boolean expression “10 > 100” is FALSE.

Here is a example using IIF

SELECT NationalIDNumber,
       IIF(Gender='F','Female','Male')
FROM   HumanResources.Employee

Here are the results

Nesting IIF Statement in SQL

You can nest IIF statements.  For instance

SELECT NationalIDNumber,
       Gender,
       MaritalStatus,
       IIF(Gender='F',IIF(MaritalStatus='S','Single Female', 'Married Female'),
                      IIF(MaritalStatus='S','Single Male', 'Married Male'))
FROM   HumanResources.Employee

Returns the following values

In this example the first IIF test for Gender.  If the Gender is ‘F’ then the green IIF (the one corresponding to the true value position) is evaluated; otherwise the IIF located in the false value position is evaluated.

Though the above example works, it is hard to read.  Before we move on can you think of a better way to write the expression?

Here is what I came up with…

SELECT NationalIDNumber,
       Gender,
       MaritalStatus,
       IIF(MaritalStatus='S','Single', 'Married') +
       ' ' +
       IIF(Gender='F','Female', 'Male')
FROM   HumanResources.Employee

I think this is easier to read.  The lesson to take from this is that though you can nested IIF statements, they are harder to read, and if you can, look for another way to write the expression.

In fact the IIF function is a shortcut for a CASE statement.  Here is the same example as a case statement.

SELECT NationalIDNumber,
       Gender,
       MaritalStatus,
       CASE
           WHEN MaritalStatus = 'S' Then 'Single'
           ELSE 'Married'
       END +
       ' ' +
       CASE
           WHEN Gender = 'F' Then 'Female'
           ELSE 'Male'
       END
FROM   HumanResources.Employee

Though nested IIF’s are harder to read, I think they are really useful when the expressions are simple.  They are compact; however, if your tests are complex or more than two, which lends itself to nesting IIF’s, then I would tend to use a searched CASE statement.

See Also:

SQL Logical Functions >>