Hi,

I have a stored procedure with a flag param.. let's call it 'iscurrent'. Based on the value of iscurrent, which block of code would you use in your stored procedure?.. basically, I'm wondering if it makes sense to have a check of iscurrent at every iteration in the WHILE loop or execute a separate block completely when iscurrent = 1 ( it just makes the procedure longer and there is repeating of certain statements that are common to both cases, that is when iscurrent = 0 or 1) .. thanks!

Sorry about the lack of indentation -- it's doesn't seem to work!

SOLUTION 1 --
IF iscurrent = 1 THEN
WHILE (endInterval <= endTime) DO
-- statement 1
-- statement 2
--- extra statements that need to be executed when iscurrent = 1
END WHILE;

ELSE
WHILE (startTime <= endTime) DO
-- statement 1
-- statement 2

END WHILE;
END IF;

OR


SOLUTION 2 --

WHILE (startTime <= endTime) DO
-- statement 1
-- statement 2
IF iscurrent = 1 THEN
---- statement 3
ELSE
---- statement 4
END IF;

END WHILE;

SOLUTION 1 looks better as if is executed only once

I think so too.. the checking of the flag at every iteration seems like it will add some overhead.. but solution 1 has a bunch of statements that are repeated in the IF and ELSE blocks so was wondering.. so the question is more lines of code, less overhead or less lines of code, more overhead..

the checking of IF statement will actually not add sinificantly overhead. the problem your stored procedure running slow is that do not mix procedure languge like IF, WHILE etc with SQL like insert, update, select and delete. this causes context switch which means procedure language execute in an engine while SQL executed in another engine by db2. in your case, try to rephase the WHILE statement see if the SQL could be executed only once.