IIF and CHOOSE Statements in SQL

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. The IIF in SQL Server gives one of two values, based on whether the Boolean phrase assesses to genuine or wrong in SQL Server. The important thing to remember here is that this IIF is a simple method for composing a CASE expression. It considers the Boolean expression entered as the initial argument and then gives either of the other two values depending on the output.

In other words, the true_value is given if the entered expression is true, and the false_value is returned if the entered expression is false or unspecified. The identical directions that use the CASE expression for Boolean expressions, null, and return types are also employed to IIF.

The point that IIF is decoded into CASE also influences other elements of the conduct of this function. IIF is employed to other servers as an identical CASE expression, with all the manners of a CASE expression.

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.

Remarks

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

CHOOSE Statement in SQL

CHOOSE statement is introduced in SQL Server 2012. CHOOSE allows selecting members of an array based on an integer index value. The CHOOSE is a logical function that gives the object at the particular or defined index from a checklist of values in SQL Server. This logical function works like a checklist into an array. In this, the array is comprised of the parameters that track the index parameters. The index parameters decide which of the subsequent values will be given.

A logical function executes an analytical process or comparison on entities and phrases and produces a Boolean value. Logical functions are important in Multidimensional Expressions (MDX) to select the place of a member.

Logical functions deliver a method to utilize logical situations to express one of several values. Users can utilize logical functions to examine a domain’s value. CHOOSE is a significant method to decide one value from a checklist of indexed values, whereas IIF delivers consolidated standards to deliver the exact type of conditional testing encountered within the CASE statement.

Its syntax is as follows

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

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

Based on the index value, the output is selected. For example, if the value of the index is 1, then val_1 is selected; if the index value is 2 then val_2 is selected, and likewise, the 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 the NULL constant in CHOOSE, but we can by using variables.

Example

Below is one beautiful example which is from BOL.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.