Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

fetching multiple results sets from stored procedure

2 posts in General Discussion Last posting was on 2003-01-14 15:49:30.0Z
Ton van den Broek Posted on 2003-01-14 15:44:07.0Z
From: "Ton van den Broek" <ton@tvdb.nl>
Subject: fetching multiple results sets from stored procedure
Date: Tue, 14 Jan 2003 16:44:07 +0100
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <H2t1Jc#uCHA.198@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: dyn.dailup.c227129097.isd-holland.nl 213.227.129.97
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:316
Article PK: 2139

Hi all,

Hope somebody more experienced on cursors/stored procedures can help me with
this ....

I have a stored procedure ("G_SpSomeProc") that returns MULTIPLE results
sets
(It calls itself recursively) which I want to store in a temporary table via
another
stored procedure (G_SpWrap) which uses a cursor and the RESUME statement
(see the code below) in order to get the result sets from this recursive
procedure

For some reason all the rows in this temporary table all contain the
same data (actually the first result). It seems that the RESUME command
doesn't
update the "rsomeres" value .... After completion of the procedure, the
number of rows
in the temporary table is as expected. But how can I get the right data in
it?

(Whenever I call the "G_Spsomeproc" procedure from within ISQL and type
RESUME
the result sets that are returned are correct)

Thanks in advance
and best regards,

Ton van den Broek

============================

alter procedure dbo.G_SpWrap(in in_artsiteid integer,in in_artnum integer)
result(rsomeres integer)
begin
declare rsomeres integer;
//
declare local temporary table ltemp(
someres integer null,
) on commit delete rows;
declare err_notfound exception for sqlstate value '02000';
declare cur_mix no scroll cursor for call
G_SpSomeProc(in_artsiteid,in_artnum,null);
open cur_mix;
lbl1: loop
fetch next cur_mix into rsomeres;
if sqlstate = err_notfound then
leave lbl1
end if;
lbl2: loop
insert into ltemp(someres) values(rsomeres);
resume cur_mix;
if sqlstate = '01W05' then
// SQLSTATE_PROCEDURE_COMPLETE
leave lbl1
end if
end loop lbl2
end loop lbl1;
close cur_mix;
select* from ltemp
end


Ton van den Broek Posted on 2003-01-14 15:49:30.0Z
From: "Ton van den Broek" <ton@tvdb.nl>
References: <H2t1Jc#uCHA.198@forums.sybase.com>
Subject: Re: fetching multiple results sets from stored procedure
Date: Tue, 14 Jan 2003 16:49:30 +0100
Lines: 7
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <I9Wbye#uCHA.344@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 213.227.150.114
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:315
Article PK: 2135

Sorry,
wrong news group (see . sybase.public.sqlanywhere.general.)

Best regards,
Ton