Re: Return Multiple Record Formats with One Stored Procedure - SOLVED

Michael -

Thank you very much; you pointed me in exactly the right direction! After doing so more searching, I found this in the archives:

http://archive.midrange.com/rpg400-l/200406/msg00305.html

which explains that the only way to return multiple record sets is to declare cursors -- if you use SET RESULT SETS ARRAY, with a MODS, you can only return one record set.

I also found this article, which was somewhat helpful:

http://www.itjungle.com/mgo/mgo121703-story01.html

although I found that some of its points are inaccurate (for example, I didn't need to use adUseClient for the cursor location).

Now I have one stored procedure that returns six record sets and a parameter with some status information, and it's all working great. Thanks again!


RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx> writes:

Yes you can return multiple datasets. I'm not sure how to do it the way you are doing it. However, I can show you a recent program I just wrote.

First I declared 3 cursors with the keywords of "WITH RETURN" in it.

Exec SQL
DECLARE R1 CURSOR WITH RETURN FOR
SELECT * FROM QTEMP/REGIONDESC;

Exec SQL
DECLARE A1 CURSOR WITH RETURN FOR
SELECT * FROM QTEMP/AREADESC;

Exec SQL
DECLARE U1 CURSOR WITH RETURN FOR
SELECT * FROM QTEMP/UNITDESC;

Each of these tables are built in qtemp. So after I populate the files.

I simply Open each cursor and return from the subprocedure. You must leave the cursors open on the return.

Exec SQL Open R1;
Exec SQL Open A1;
Exec SQL Open U1;
// This procedure returns all three result sets, if you don't
// specify the "SET RESULT SETS" instruction then the procedure
// will return all three in the order they were opened.
Return;

Then the creation of the SQL procedure was defined like so...
CREATE PROCEDURE BEFLIBT.GETALLUNITDESC (
IN COMPANY CHAR(3) ,
IN DIVISION DECIMAL(3, 0) ,
IN FISYR DECIMAL(4, 0) ,
IN FISPD DECIMAL(2, 0) )
DYNAMIC RESULT SETS 3
LANGUAGE RPGLE
SPECIFIC BEFLIBT.GETALLUNITDESC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'BEFLIBT/BEFG@RAUDN(GETALLUNITDESC)'
PARAMETER STYLE GENERAL ;

Your procedure cannot return more result sets than what you define on the "DYNAMIC RESULT SETS 3" instruction on the stored procedure.

Hope this helps.

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.