IIF

IIF is an acronym used in programming and data query languages to denote a conditional expression that returns one of two values depending on a condition. It is commonly described as an "immediate if" or an if-then-else function, and its precise behavior varies by language and implementation.

In Visual Basic and its derivatives (VBA, VB6), the IIf function takes three arguments: IIf(condition, valueIfTrue, valueIfFalse). It returns valueIfTrue when the condition is true, otherwise valueIfFalse. A notable characteristic is that both valueIfTrue and valueIfFalse are evaluated before the function returns, which can cause errors or unwanted side effects if either branch has problematic expressions.

SQL Server provides a built-in IIF function starting with SQL Server 2012: IIF(condition, trueValue, falseValue). It serves as a shorthand for a CASE expression. Like many language features, the exact evaluation behavior may depend on the engine, and there can be caveats around evaluating the non-selected branch, so developers should be mindful of potential side effects or type issues.

Other environments, such as Crystal Reports formula language, also support IIF with the same general syntax: IIF(expression, trueValue, falseValue). In these contexts, IIF is a convenient alternative to longer conditional constructs, but portability across systems is limited since IIF is not part of a universal standard.

Alternatives to IIF include the CASE expression in SQL and the ternary operator in many programming languages. When using IIF, considerations include short-circuiting behavior, type compatibility of return values, and how nulls are handled.