Tags: building, cursor, database, db2, dynamic, loop, mysql, oracle, procedure, sql, stored, writing

While Loop for Cursor

On Database » DB2

1,365 words with 1 Comments; publish: Wed, 05 Dec 2007 00:06:00 GMT; (25093.75, « »)

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!

All Comments

Leave a comment...

  • 1 Comments
    • 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

      #1; Tue, 11 Dec 2007 17:52:00 GMT