Calling a nested stored procedure in DB2

Yogesh Pandit

Hi,

I have 2 stored procedures A and B. A makes a call to B and B returns a cursor to A. Stored Procedure B executes fine individually. However, when I execute A, I get the following error:

SQLState = 07003
SQLCode = -518

Code snippet for stored procedure B:

DECLARE SampleCursor CURSOR WITH RETURN TO CALLER FOR
SELECT *
FROM TABLE
FETCH FIRST 5 ROWS ONLY;

OPEN SampleCursor

Code snippet for stored procedure A:

DECLARE LOC1 RESULT_SET_LOCATOR VARYING;

CALL B(OUT_SQLCODE, OUT_SQLSTATE, OUT_MESSAGE);

ASSOCIATE RESULT SET LOCATOR (:LOC1) WITH PROCEDURE B;
ALLOCATE CSR1 CURSOR FOR RESULT SET :LOC1;

EXECUTE CSR1 USING LOC1;

Can anyone please help me with this?

Aug 7 '07 #1

chatzeey

Do not use the following execute statement. Just fetch that cursor in the stored procedure in order to read the data or get the result set from other applications by calling the stored procedure.

EXECUTE CSR1 USING LOC1; --Not required, as Allocate will open the cursor.

Sep 19 '07 #2