These are new switching functions that were once available only on Microsoft Access. They are syntactic sugar for
CASE expressions and compile to the same plans (source: IIF, CHOOSE).
IIF ( boolean_expression, true_value, false_value )
CHOOSE ( 1-based-index, val_1, val_2 [, val_n ] )
Note: Both these functions cast their output to the data type with the highest precedence from the set of types passed in as arguments.
SELECT IIF(1 = 1, 'true', 'false') iif_example;
SELECT CHOOSE(3, 10.3354, 'It slices!', 1337, N'It dices!') choose_example;
Note how in the second example the output is
1337.0000. That's because
10.3354 gets implicitly cast to
NUMERIC(8, 4), which has the highest data type precedence in the list of arguments passed to
CHOOSE(). Thus, the output also get cast to
NUMERIC(8, 4), which is why you see four trailing zeros after the decimal.