IIF and CHOOSE statements in SQL

Written on May 11, 2016

IIF Statement in SQL

IIF statement is introduced in SQL Server 2012. We can say it's a shorthand version of CASE statement. If you come from .net world, you can relate IIF in SQL with IIF in visual basic language. Its syntax is as below.

IIF(Boolean_expression, true_value, false_value)

Here, Boolean_expression can be any predicate. If its outcome is true then true_value is selected. If its outcome is false or null then false_value is selected. Below is an example of IIF.

  1. We cannot directly pass NULL constant in IIF, but we can by variables.
  2. We have CASE statement nested up to 10 level. As IIF is shorthand version of CASE, this limitation also apply to IIF.


CHOOSE Statement in SQL

CHOOSE statement is introduced in SQL Server 2012. CHOOSE allows to select member of array based on an integer index value. Its syntax is as follow.

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

Here index is 1-based. It can be only of numeric, but not other type. For example, if we pass decimal as index, it'll convert it to integer. But in-case we pass string as index, then it'll give an error.

Based on index value, output is selected. For example, if value of index is 1, then val_1 is selected; if index value is 2 then val_2 is selected; and likewise list goes on.

In case we have passed index as 4, and there are only 2 elements it'll give NULL as result. Also, we cannot pass NULL constant in CHOOSE, but we can by using variables.


Below is one beautiful example which is from BOL.