I was looking for some information on short-circuit logic in stored procedures. The system in question is V6R1.
Using System i Navigator to set break points and debug a job, I have found that CASE or IF statements in procedures that are designed to execute sub-queries or function calls only in certain situations are executing regardless of the values in the condition.
For a simple example, if I had a procedure doing something like:
Select * from Table Where (InputParameterCondition = 'N' or (InputParameterCondition = 'Y' and FunctionToTestSomeComplexData() = 'Y'))
Here we are trying to select only certain records when a filter condition is set on or return everything and don't execute the complex function when the condition is off. However, even when the parameter is "N", the function executes. The end result is correct: it filters the data as expected, but the extra processing still occurs when the filter is off, which is obviously not desired.
Similarly, if I had:
Select Field1, Field2, (Case When InPutParameterCondition = 'N' then '' When InPutParameterCondition = 'Y' then GetSomeComplexData() End) as ConditionedColumn From Table Where...
I would expect the function to execute only when the parameter is "Y" but it executes all the time. The value returned is correct, but again, the extra processing occurs.
Researching this indicates that SQL doesn't always guarantee short-circuit logic. Is this correct for DB2 and should I expect what I've seen?