Hi,

I am absolutely new to DB2 and am trying to write a DB2 sql procedure. The code is :

CREATE PROCEDURE COUNTRY1 (IN i_perinc VARCHAR(2))
LANGUAGE SQL
Begin

DECLARE c1 CURSOR WITH RETURN FOR
SELECT country_code, country_name FROM country where country_code = i_perinc;
OPEN c1;
END

Now I am trying to call this SP from another SP given below:

CREATE PROCEDURE Test1 ( IN Name CHAR(4) )
LANGUAGE SQL
DYNAMIC RESULT SETS 1

P1: BEGIN
-- Declare cursor
Declare v1 VARCHAR(2);
DECLARE cursor1 CURSOR FOR
SELECT AIRPORT_CODE FROM AIRPORT AS AIRPORT WHERE AIRPORT.AIRPORT_CODE = Name;
OPEN cursor1;
fetch cursor1 into v1;
call country1(v1);

END P1


When I call the SP from command editor by giving the command :
call Test1('22')

I end up with the following error :

SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501

SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.

Explanation:

The program attempted to either: (1) FETCH using a cursor, or (2)
CLOSE a cursor at a time when the specified cursor was not
open.

The statement cannot be processed.

User Response:

Check for a previous message (SQLCODE) that may have closed the
cursor. Note that after the cursor is closed, any fetches or
close cursor statements receive SQLCODE -501.

If no previous SQLCODEs have been issued, correct the
application program to ensure that the cursor is open when the
FETCH or CLOSE statement is executed.

sqlcode : -501
sqlstate : 24501

I am unable to fix the error, pls help.

What is your intent for SP test1? If it is to return the result set from the Country1 SP, then you need to change the cursor in Country1 from "WITH RETURN" to "WITH RETURN TO CLIENT".

Andy