Tags: building, cursor, database, db2, dynamic, loop, mysql, oracle, procedure, sql, stored, writing
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!
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;
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.
Andy#1; Tue, 11 Dec 2007 17:52:00 GMT