CODEWITHSUNDEEP
sqlstored-proceduresdb2cursor
Viking
Viking

Reputation: 83

Two cursors sequence in procedure

I have the following problem with a procedure:

CREATE PROCEDURE TEST() 
LANGUAGE SQL
BEGIN

DECLARE V_TEST VARCHAR(100);
DECLARE V_TEST_EXT VARCHAR(100);
DECLARE SQLCODE INTEGER DEFAULT -1;
DECLARE RET_CODE INTEGER DEFAULT -2;
DECLARE LIST_CMD VARCHAR(512);
DECLARE LIST_CMD_EXT VARCHAR(512);

DECLARE CUR_TEST CURSOR WITH RETURN FOR LIST_STMT;
DECLARE CUR_TEST_EXT CURSOR WITH RETURN FOR LIST_EXT_STMT;

DECLARE CONTINUE HANDLER FOR SQLEXECPTION SET RET_CODE = SQLCODE;

SET LIST_CMD = 'SELECT TEST FROM TESTTAB';
PREPARE LIST_STMT FROM LIST_CMD;

OPEN CUR_TEST;
FETCH CUR_TEST INTO V_TEST;
    WHILE (RET_CODE <> 100) DO
        FETCH CUR_TEST INTO V_TEST;
    END WHILE;
CLOSE CUR_TEST;

SET LIST_CMD_EXT = 'SELECT TEST FROM TESTTAB';
PREPARE LIST_STMT_EXT FROM LIST_CMD_EXT;

OPEN CUR_TEST_EXT;
    FETCH CUR_TEST_EXT INTO V_TEST_EXT;
    WHILE (RET_CODE <> 100) DO
        FETCH CUR_TEST_EXT INTO V_TEST_EXT;
    END WHILE;
CLOSE CUR_TEST_EXT;

END;

The problem I am having is that the procedure does the first cursor loop but ignores the second one. I tried adding BEGIN and END to each loop but that didn't help as well.

I am running DB2 v10.5 Windows. I need this to work as a procedure. Is there any way to make the procedure run both loops?

Thank you for your help.-

Upvotes: 0

Views: 386

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

You don't reset the RET_CODE variable before processing the 2-nd cursor. Use something like this:

SET RET_CODE = 0;
OPEN CUR_TEST_EXT;
...

BTW, don't declare a cursor using 'WITH RETURN' clause if you process this cursor inside the procedure.

Upvotes: 2

Related Questions