multiple result set
Chuan Lu asked how to return multiple result sets from an Informix stored procedure. Ping Tang and Art Kagel said to loop and use RETURN ... WITH RESUME, with clients fetching it like a multi-row query. Chuan clarified wanting two different result sets from two foreach loops in one procedure. No answer on that in the thread.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, Do anyone know how to return mulitple result set in informix store procedure? thanks.
use WITH RESUME option. RETURN <list of return values> WITH RESUME; Regards, -Ping --- On Sun, 6/7/09, CHUAN LU <luchuan114@sina.com> wrote: From: CHUAN LU <luchuan114@sina.com> Subject: multiple result set [15961] To: ids@iiug.org Date: Sunday, June 7, 2009, 11:48 PM Hi, Do anyone know how to return mulitple result set in informix store procedure? thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Most everyone ;-) You need to get your results in a loop within the procedure and then use RETURN .... WITH RESUME. Clients treat the procedure call like any other multi-row query and use cursors or the equivalent to fetch rows. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 8, 2009 at 12:48 AM, CHUAN LU <luchuan114@sina.com> wrote: > Hi, > > Do anyone know how to return mulitple result set in informix store > procedure? > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5bd8c89d672046bd43507
The result set could include more than one rows in store procedure, What I want to do like the following. Create procedure .... ..... foreach select c1,c2,c3 into v_c1,v_c2,v_c3 from tb1 where condition return v_c1,v_c2,v_c3 with resume; end foreach; ...... foreach select c1,c2,c3,c4 into v_c1,v_c2,v_c3,v_c4 from tb2 where condition return v_c1,v_c2,v_c3,v_c4 with resume; end foreach; end procedure; I want to return two result sets in one store procedure.