Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New LOGICAL functions in SQL Server Denali

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

Capture

I am not so sure where I will be using this function in the future, but the IFF was love at first sight :)

@ms1333

Comments

Posted by david.howell on 6 September 2011

I find IIF very handy in Reporting Services and VB.NET. No such thing in C# unfortunately.

You can also use it in MDX although you may need to be careful about performance:

sqlblog.com/.../performance-of-iif-function-in-mdx.aspx

Posted by honza.mf on 6 September 2011

IIF in C#? Ternary operator ?: does the work.

Posted by j.summers on 7 September 2011

IIF is an implementation of the C-language ? operator, which is found in derived languages including C#, C++ and Java.

Example:

int result = (booleanExpression)? 1: 2;

Posted by karl.gjertsen on 7 September 2011

The ternary operator seems to be a hidden feature of C#, but it does exist.

Posted by pipTheGeek on 7 September 2011

I don't think the ternary operator is any more hidden than any other operator.  Also remember, IIF is a function (In VB), ALL the expressions will be evaluated regardless of the result of the test expression.  In C# ?: is an operator and only the expression being returned will be evaluated.

Posted by hugo on 7 September 2011

To be precise:

IIf in VB has no equivalent function in C#.

If in VB.Net is a ternary operator like ?: in C#. So the If operator in VB.Net does not evaluate all expressions.

Posted by Anonymous on 7 September 2011

Pingback from  Dew Drop – September 7, 2011 | Alvin Ashcraft's Morning Dew

Posted by Matt Wright on 7 September 2011

Your heading calls the function IFF, but your code calls it IIF. Which is correct?

Posted by Ernie Schlangen on 7 September 2011

It's IIF (Immediate IF) as I recall.

Posted by thermanson on 7 September 2011

Let the IIF vs Case performance tuning arguments begin.

Posted by wolfkillj on 7 September 2011

When I worked primarily with SSRS, I used the CHOOSE function occasionally. I usually derived the index from a parameter or column value using nested IIF functions, so in essence, it functioned like a CASE expression, like this:

CHOOSE(IIF(column_name = "AAA", 1, IIF(column_name = "BBB", 2, 0)), "Alexandra", "Bill")

as the equivalent of

CASE WHEN column_name = 'AAA' THEN 'Alexandra'

WHEN column_name = 'BBB' THEN 'Bill'

ELSE NULL

END

However, if there were more than a couple possible values of the input (column_name, @param, whatever), I found it a lot easier and cleaner to use the SWITCH function because its syntax is much more akin to a SQL CASE expression. Here's a set of examples of many of the VB functions as they can be used in SSRS:

msdn.microsoft.com/.../ms157328.aspx

Posted by Mike Dougherty-384281 on 7 September 2011

I don't understand why we need another way to do Case.  

It's like using isnull() when coalesce() does the same job (and replaces nested isnull)  If thermanson proves that IIF minimally outperforms the simple usage of Case, are we going to microOptimize code with IIF?

That choose construct seems to be more likely to be used to support bad schema than any noble purpose.  Even the example "TheRealDeal" is contrived.  Shouldn't the choices be in a table and retrieved via a join on what is currently the choose() function's first parameter (index)?

Posted by Robert Domitz on 7 September 2011

The CHOOSE function is useful when selecting error and status messages to return directly to a user in something similar to the following:

DECLARE @status INTEGER

SET @status = EXEC stored_function.....

CHOOSE (@status, PRINT message1, PRINT message2,....

The CHOOSE statement may be in another stored procedure which also provides the mechanism for returning the message to the user in a more effective method than PRINT.

Posted by brandon.leach on 7 September 2011

Looks like choose is to iif as iif is to case, but with a twist.  If you have a integer parameter perhaps choose can make the syntax more readable.  Not sure I see much in it beyond eliminating the need for a case in certain circumstances.  I wonder what the performance implications are for choose vs case. Off to research I go.

Posted by Joe Celko on 7 September 2011

What a disaster! It looked like MS was heading toward ANSI/ISO Standards and away from their old "Codse Museum" they screw it up. Now good SQL Programmers will have to add another macro to their "pretty printer" to turn this hillbilly code into real SQL.

I had so much hope when we finaqlly got the [ROW|RANGE] subclause in the Window clause ...

Posted by Joe Celko on 7 September 2011

What a disaster! It looked like MS was heading toward ANSI/ISO Standards and away from their old "Codse Museum" they screw it up. Now good SQL Programmers will have to add another macro to their "pretty printer" to turn this hillbilly code into real SQL.

I had so much hope when we finaqlly got the [ROW|RANGE] subclause in the Window clause ...

Posted by Thiago Dantas on 7 September 2011

IIF probably translate to CASE WHEN in the exec plan

Posted by Geniiius on 7 September 2011

Sorry about the typo - here is a link to a version without then, dunno when or if this syndication wil update.

www.geniiius.com/.../new-logical-functions-in-sql-server-denali

Posted by Ken on 10 September 2011

wolfkillj's use of CHOOSE seems unnecessary.

IIF(column_name = "AAA", "Alexandra", IIF(column_name = "BBB", "Bill", NULL))

should return the same result without the additional command processing

Posted by Anonymous on 12 September 2011

Microsoft Dynamics ERP Business Ready Licensing Module Offer (Kevin Machayya) - Effective September 1

Leave a Comment

Please register or log in to leave a comment.