IFF Function

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

CHOOSE Function

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

@ms1333