If you are an old Access or Excel “developer” you know this one. This is not a function I have been missing before I read about it today. If you, like me, is so tired of writing CASE statements with only 2 possible outcoms – you will love this new function.
IIF returns one of two values depending on the Boolean expression. Here is the syntax from BOL
IIF ( boolean_expression, true_value, false_value)
Let’s have alook at an example with sys.master_files, where I am looking at the Growth column. This is how it would look before Denali:
WHEN 1 THEN CAST(growthas VARCHAR) +' %'
ELSE CAST((growth*8) AS VARCHAR) +' KB'
END AS FileGrowth,
Pay attension to the CASE – and here how it is done with the new IIF statement:
is_percent_growth = 1,
CAST(growth as varchar) + ' %',
CAST((growth * 8) as Varchar) + ' KB') AS FileGrowth,
The Choose function returns the item at the specified index from a list of values.
CHOOSE (index, val_1, val_2 [, val_3])
The Choose function is like an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.
Let’s have a look at a little example:
SELECT CHOOSE( 2, 'http://www.geniiius.com','http://www.geniiius.com/blog','http://www.geniiiusCRM.com') AS TheRealDeal
And the result
I am not so sure where I will be using this function in the future, but the IIF was love at first sight.