64

I recently learned of the IIF(A,B,C) function. I'm a long time VB/VB.NET Coder who recently spent a lot of time coming up to speed in SQL coding.

One (obvious) common thing to do in SQL is something like the following:

select (case where @var = 0 then MyTable.Val1 else MyTable.Val2 end) from MyTable

IIF(A,B,C) will allow me to do this in VB.NET... all on one line.

However, I have read that both B and C are evaluated no matter what A evaluates to.

I can think of some obvious situations where this is a bad thing such as:

Dim X as integer = IIF(SomeBoolean = true, ExpensiveFunction1(), ExpensiveFunction2())

As I will be including this in my repertoire, are there any more subtle situations where I may get myself into trouble using IIF?

It's a pretty big departure in some situations from using the old fashioned:

Dim X as integer
if SomeBoolean = true then
  X = ExpensiveFunction1()
else
  X = ExpensiveFunction2()
end if

I'm hoping to save myself some annoying performance issues and/or bugs in the future.

Update 2016

For the past few years, a new VB.NET feature exists which eliminates the need to use the IIF() function.

if(Something = true, ExecuteA(), ExecuteB())

Only ExecuteA() OR ExecuteB() are executed. Finally, inline IF with shortcircuiting.

So, if you are using later versions of VB.NET (as of 2016), use this instead if you can.

Community
  • 1
  • 1
Brian Webster
  • 30,033
  • 48
  • 152
  • 225
  • This Works using asp.net 4.6.1 in VS 2015. – dmd733 Sep 19 '16 at 02:52
  • 1
    @AdamNaylor 's link is not valid anymore (Jan 2017) :-( – pashute Jan 03 '17 at 21:41
  • 1
    @pashute : The WaybackMachine is your friend :-) https://web.archive.org/web/20141108075055/http://www.adamjamesnaylor.com/2012/10/29/SQL-Server-Reporting-Services-IIf-Function-Evaluating-True-And-False-Parameters.aspx – Goozak Jan 31 '18 at 14:52

5 Answers5

42

Here is the most common gotcha.

Z = iif(y=0, 0, x/y)  'Throws a divide by zero exception when y is 0

Don't use it to avoid division by zero errors.

Another possible logic bug is when one side of the iif or the other calls a method that modifies the system state or has output parameters.

Z = iif(FunctionA(InputOutputParam), FunctionB(InputOutputParam))
'InputOutputParam is indeterminate or at least ambiguous here.

There really is no good reason to use IIF in my experience. Mostly it is just used to abbreviate code and given the problems it can cause, it just isn't worth it. Plus, I think it makes the code harder to read.

The other thing that bites is that it returns a boxed value (ie an Object data type) which you have to cast back to the desired type.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • throws a divide by zero regardless of whether or not y is zero. – Joel Coehoorn Aug 03 '09 at 03:00
  • 4
    You sure, Joel? I'm rechecking this, but don't see how it would throw a divide by zero if y=5, for example). Assuming VB.NET of course, for C# I see your point, but the tag on the OP question specified otherwise. – JohnFx Aug 03 '09 at 03:36
  • 1
    +1 I just ran into this and the C# implementation is clearly better. `IIF (Language != VB.Net, Success, throw new( TriviaException ))` – Jeremy Thompson Nov 17 '14 at 05:10
  • It's a good point about divide by zero. There is legitimate use of IIf(). That is, in Access queries, an inline if is the only option I know of for inline logic. It would be better if it didn't evaluate both sides, of course. – someprogrammer Aug 30 '18 at 15:03
41

[IIF, not IFF]

The most common case we've seen is that one side or the other evaluates to Nothing.

Your code might be expecting to use IIF as guard to keep from getting a NullReferenceException, like this:

IIF(something Is Nothing, "nothing", something.Value)

But that won't work, because both sides are always evaluated. This happens a lot in code written by people who come from a C/C++/C#/Java background, since in those languages the ternary operator ?: does short-circuit evaluation.

And the fact that the VS 2005 IIF() documentation states that IIF is just like ?: doesn't help:

The IIf function provides a counterpart for the ternary Conditional Operator: ? : in Visual C++.

Nowhere on that reference page does it state that both sides are evaluated.

lavinio
  • 23,931
  • 5
  • 55
  • 71
  • 2
    +1 for clarity. The 2008 documentation was updated to explicity mention the lack of short-circuit evaluation, and recommends using `If` instead. – EvilDr Jul 22 '16 at 10:55
34

According to MSDN, the "If" operator introduced in VB2008 will short-circuit instead, which would be ideal for your expensive computation case:

http://msdn.microsoft.com/en-us/library/bb513985.aspx

Amber
  • 507,862
  • 82
  • 626
  • 550
  • I think this is only true if you use the new Andalso and OrElse operators. AND/OR still don't use shortcut Boolean evaluation for backward compatibility with VB6. – JohnFx Aug 03 '09 at 02:04
  • I much prefer If() or IIf() just for this reason. – Sukasa Aug 03 '09 at 02:04
  • 3
    The If *operator* has nothing to do with the AndAlso and OrElse operators, JohnFx. It is a separate, standalone trinary operator. – Amber Aug 03 '09 at 02:09
  • Sorry, I missed the word "operator" in your answer. Just to avoid confusion for the original poster, the AndAlso and OrElse operators only apply when you are using If ... not If(). (Did they make this confusing or what?) – JohnFx Aug 03 '09 at 02:18
  • This is why keywords and operators should never share names. ;) – Amber Aug 03 '09 at 02:22
  • No kidding. That is just asking for trouble. – JohnFx Aug 03 '09 at 03:38
  • 2
    So it would appear that the If() operator linked to in the above answer causes IIF to be obsolete. Is there any conceivable reason why somebody would use IIF() when If() is available? (Note: IF() does not evaluate both the *true* and *false* cases). Am I missing something? – Brian Webster Aug 03 '09 at 10:49
  • 4
    @hamlin11: No, you should always use If() IMO. Iif() was probably kept for backwards compatibility. – Meta-Knight Aug 03 '09 at 12:39
  • Dav: I appreciate your information because it shows a better alternative, but I'll be accepting another answer that answers my actual question. Sorry & Thanks – Brian Webster Aug 04 '09 at 21:48
  • @Meta-Knight : I guess some people may want to use IIF because of the side-effects it can provide, although this is propable a really bad way to program. – David Brunelle Apr 18 '13 at 18:54
1

Well, you should also make sure you don't have any functions in in iif that modifies any data based on the condition. We use If for a rather lot of that. It just pays to remember that about iif.

Cyril Gupta
  • 13,505
  • 11
  • 64
  • 87
0

I was forced into using an iif (for compactness of code) where I had a chunk of code that was copying values out of many arrays into a spreadsheet, but "nothing" entries in the array causes the code to exit the subroutine (not crash), so I wrapped the line in an iif to check if the array cell contained nothing - if it did, then pass back "" otherwise, pass back the array cell (converting to a string 1st). So as said above, another reason not to use iif.

I'm still in mourning due to the lack of the NZ function that I used all the time in MS Access.

Kristian
  • 406
  • 4
  • 12