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.
IFF returns one of two values depending on the Boolean expression. Here is the syntax from BOL
IFF ( 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:
select database_id, file_id, type_desc, name, physical_name, CASE is_percent_growth WHEN 1 THEN CAST(growthas varchar) +' %' ELSE CAST((growth*8) as Varchar) +' KB' END as FileGrowth, size from sys.master_files
Pay attension to the CASE – and here how it is done with the new IFF statement:
select database_id, file_id, type_desc, name, physical_name, IIF(is_percent_growth = 1, CAST(growth as varchar) + ' %',CAST((growth * 8) as Varchar) + ' KB') as FileGrowth, size from sys.master_files
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 IFF was love at first sight