Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003

    Unanswered: While Loop for Cursor

    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!

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    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
    FETCH FROM MyCursor into ....

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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts