All,

I have two stored procs that both return resultsets and i would like to have a main stored proc and use this to call one or the other of my two stored procs.
I would then like to return the resultset of the one I call back to the client.

I have the following but it doesn't work, can't find much reference to it. Can anyone help?

CREATE PROCEDURE MAINONE(IN MYVAR VARCHAR(50))
RESULT SETS 1
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN TO CALLER FOR

IF MYVAR='1' THEN
CALL PROC_ONE;
ELSE
CALL PROC_TWO;
END IF;

-- REALLY NOT SURE ABOUT NUMBER OF CURSORS TO OPEN OR TO RETURN ----TO WHAT? NOT SURE OF GENERAL STRUCTURE?

BEGIN
DECLARE C2 CURSOR WITH RETURN TO CLIENT FOR;
C2 = C1;
OPEN C2;
END;

END
@

That's simple: in the nested procedure, you have to say WITH RETURN TO CLIENT and open the cursor there. The outer procedure doesn't care about cursors at all.

Do you mean as below? If so, this doesn't work. I get an error saying 'An unexpected token "CALL" was found following "RETURN TO CLIENT FOR"

BEGIN

IF MYVAR='1' THEN
DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR
CALL PROC_ONE;
open C1;
ELSE
DECLARE C2 CURSOR WITH RETURN TO CLIENT FOR
CALL PROC_TWO;
open C2;
END IF;



END

The 1st cursor is declared inside PROC_ONE and the 2nd cursor inside PROC_TWO. At those declarations, you have to say WITH RETURN TO CLIENT. You don't have to care about those cursors in your main procedure, i.e.