Specifying the action that the row cursor is to take when it reaches the end of the data

Your program must be coded to recognize and handle an end-of-data condition whenever you use a row cursor to fetch a row.

About this task

To determine whether the program has retrieved the last row of data, test the SQLCODE field for a value of 100 or the SQLSTATE field for a value of '02000'. These codes occur when a FETCH statement has retrieved the last row in the result table and your program issues a subsequent FETCH. For example:

IF SQLCODE = 100 GO TO DATA-NOT-FOUND.

An alternative to this technique is to code the WHENEVER NOT FOUND statement. The WHENEVER NOT FOUND statement causes your program to branch to another part that then issues a CLOSE statement. For example, to branch to label DATA-NOT-FOUND when the FETCH statement does not return a row, use this statement:

EXEC SQL
  WHENEVER NOT FOUND GO TO DATA-NOT-FOUND
END-EXEC.

For more information about the WHENEVER NOT FOUND statement, see Checking the execution of SQL statements.