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
Dim X as integer = IIF(SomeBoolean = true, ExpensiveFunction1(), ExpensiveFunction2())
Dim X as integer
if SomeBoolean = true then
X = ExpensiveFunction1()
X = ExpensiveFunction2()
if(Something = true, ExecuteA(), ExecuteB())
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.