SQL -VB - Does it evaluate the else even when the if is true?

I am trying to prevent #error being displayed in a report I am creating

This happens when I divide 2 numbers and one of them is zero

So I tried using an if/switch statement to check if either of the 2 number are 0 before doing the divide :

    =IIf(Fields!Field1.Value = 0 
            or Fields!Field2.Value = 0 
            or Not(IsNumeric(Fields!Field1.Value)) 
            or Not(IsNumeric(Fields!Field2.Value)), 
        0, 
        (Fields!Field1.Value/Fields!Field2.Value)*100
    )


    =Switch(
            Fields!Field1.Value = 0 or Fields!Fie开发者_开发百科lds.Value = 0, 0,
            IsNumeric(Fields!Field1.Value) or IsNumeric(Fields!Fields.Value), (Fields!Field1.Value/Fields!Fields.Value)*100
    )

Both of these still throw the error. It seems that the else condition is still evaluated even if the if statement is true

If I change the code to just print X or Y for the if and else, then it works - so there isn't an error in the if statement

This sems ridiculous to me

Please tell me I am doing something wrong? I can't believe the language would evaluate the else when the if is true

EDIT

So it seems that the else condition is evaluated. So how do you get around a potential divide by zero error?

here's the answer taken from : http://www.sqlservercentral.com/Forums/Topic442497-150-1.aspx#bm1115960

Another option (especially if you've got a report with many expressions that could result in divide by zero situations is to use a Custom Code function.

In the Code tab/window of Report Properties, enter something like the following:

Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else : DivideBy = Exp1 / Exp2
End If
End Function

Then insert the expression =code.DivideBy(Field!ToBeDivided.Value,Field!DividingBy.Value) into any cell that has the potential for divide by zero problems.

---

Yes, both the true and false parts of an IIf function get evaluated:

http://en.wikipedia.org/wiki/IIf#Side_Effects

Side Effects
Another issue with IIf arises because it is a library function: unlike the C-derived conditional operator, both truepart and the falsepart will be evaluated regardless of which one is actually returned. Consider the following example:

value = 10 
result = IIf(value = 10, TrueFunction, FalseFunction)

Although TrueFunction is the function intended to be called, IIf will cause both TrueFunction and FalseFunction to be executed.

Also consider this one:

a = 10 
b = 0 
result = IIf(b <> 0, a / b, 0) 

While the programmer intends to avoid raising an error by performing a division by zero, whenever b is zero the error will actually happen. This is because the code in the snippet is to be read as

a = 10 b = 0
_temp1 = a / b ' Error if b = 0
_temp2 = 0
_temp3 = b <> 0 result = IIf(_temp3, _temp1 , _temp2) 

This issue makes the IIf() call less useful than the conditional operator. To solve this issue, Microsoft developers had considered converting IIf to an intrinsic function; had this happened, the compiler would have been able to perform type inference and short-circuiting by replacing the function call with inline code.

---

Both Iif() and Switch() are function calls, so any parameters passed to them will be evaluated. They do not have the ability to short-circuit.

---

IIF is a function, and all the arguments to a function are evaluated before being the function itself is evaluated.

To prevent this issue, you will need to use a full If/Else statement.

Also related, if you're used to short-circuit evaluation of your boolean expressions (eg && in C-derived languages) you need to use AndAlso and OrElse in VB.Net.

---

In the case of an IIF statement in VB, I think everything gets evaluated, irrespective of the initial condition. This is something that has to be worked around in IIF statements.

In the second statement, the Fields!Field**2**.Value from the first IIF statement has become Fields!Field**s**.Value. Its possible that the error in that statement is failing because of that as .Value will cause an error if Fields!Fields is nothing

---

I tried to reproduce this error in RS 2005 and I can't! I took the following dataset:

select 5 as field1, 0 as field2
union
select 10 as field1, 5 as field2
union
select null as field1, 5 as field2
union 
select 3 as field1, null as field2
union 
select null as field1, null as field2
union 
select 0 as field1, null as field2
union
 select null as field1,  0 as field2
union
 select 11 as field1, 10 as field2
union
 select PI() as field1, 0 as field2
union
select PI() as field1, PI() as field2

and used your expressions, with Jon Egerton's correction in the switch. I then did a pure no-checked division.

Both your expressions worked, and even when I divide by 0 I get "Infinity" or "Nan", but not #Error.

What happens if you replace your division by 1/0. Do you get #Error or do you get Infinity?