I am writing a stored procedure where the sql in the stored procedure is dynamic. After building my dynamic sql for my first cursor, I want to loop thru the cursor until there are no more. In my while statement I am evaluating each row and may or may not have to do some other calculations. My question is how do I have the while ... do continue until no more rows? I have seen the technique where you do a select count(*) from table and compare while (current row < number rows) but I don't know how many rows are coming back into my cursor because the sql statement is being built on the fly. I know in Sybase and I think Oracle has something similar you can say while (sqlcode = 0), I cannot find anything for db2.

Any help would be greatly appreciated!

There are two techniques you can try:

check the SQLCODE right after the FETCH '02000' is no more records. e.g.

DECLARE MyCursor cursor for s1;

SET myStatement = dynamic stuff;

prepare s1 from myStatement;

open MyCursor;
FETCH FROM MY_Cursor into ....
WHILE SQLCODE <> '02000' do
Process...
FETCH FROM MyCursor into ....
END WHILE;

Or you could use a CONTINUE CONDITION on SQLSTATE = '02000' and set a flag that the cursor has been used up.

HTH

Andy