Member_2_2484401
asked on
return 2 result sets from a stored proc
Greetings, experts!
I have a stored procedure that is supposed to return two result sets, but only one result set is coming back. I verified that both queries return valid data, but (for some reason) only the last result set is returned.
The procedure is actually a C-program (an "external" procedure) with embedded SQL.
Please take a look, and let me know what I'm doing wrong. I'm sure the answer is blindingly obvious, but I've been looking at this for so long that I've lost all objectivity.
Thank you very much!
DaveSlash
CREATE PROCEDURE MyProc (
... some parameters ...
)
RESULT SETS 2
LANGUAGE C NOT DETERMINISTIC MODIFIES SQL DATA
EXTERNAL NAME MYPROC
PARAMETER STYLE GENERAL WITH NULLS
... some stuff ...
exec sql
DECLARE resultset CURSOR FOR
select sectionTitle
from TTSECTDTLS
;
exec sql
DECLARE resultset2 CURSOR FOR
select freeTextID,
freetextData
from TTFOOTDTLS
;
EXEC SQL OPEN resultset;
EXEC SQL OPEN resultset2;
EXEC SQL SET RESULT SETS CURSOR resultset;
EXEC SQL SET RESULT SETS CURSOR resultset2;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since this is just between, "us guys", the payoff should be in beer. :)
ASKER
I'm all for that!
I've been homebrewing my own beer for 11 years, so if you ever make it to Southern California, I'll have a six-pack ready for you! :-)
-- DaveSlash
I'll be in San Luis Obispo in August. :)
ASKER
If you can make the 200-mile drive down to the L.A. area, I'll buy you dinner AND give you the 6-pack.
-- DaveSlash
Geez -- what is there to do in LA? :)
Let me check with my wife. She'll be accompanying me.
Kent
ASKER
Whoops. I almost forgot to close this question.
Have some points, guys. Your input and questions guided me to where I found the answer.
-- DaveSlash
ASKER
OK, go ahead and say it. I deserve it.
" READ THE STINKIN' MANUAL, DAVE !! "
So, I broke out the "DB2 for iSeries - SQL Reference" manual, and it gave me the answer:
Combine the two SET RESTULT SETS statements into one.
e.g.
SET RESULT SETS CURSOR resultset, CURSOR resultset2;
That one works beautifully.
Thanks for your guidance, gentlemen.
DaveSlash