calling a stored procedure from a stored procedure

Hi Group!

I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL.

I have a sproc which breaks a given string-parameter into pieces. It's
result set contains the pieces, e.g. last line of sproc: "select item from
mytable;".
This sproc works fine.

Now I need to call this procedure from another procedure which has a
variable result set.
I tried the following:

<snip...
set ll_retval = 0;
call sp_breakstring(as_bzp,',');
BreakLoop:
while ll_retval <> 100 and not ll_retval < 0 loop
set li_zaehler = li_zaehler + 1;
fetch next sp_breakstring into ls_tmp;
set ll_retval = sqlcode;
If not ll_retval < 0 and not ll_retval = 100 then
---snip>

That did not work with the message "cursor not open", so I tried to prepare
the sproc and declare a cursor for it. But it didn't work either.

I searched the online help but wasn't able to find an example how to get the
result sets from a sproc within a sproc.
Can someone help me here?

Another question:
I need the topmost (outer) sproc to return a variable result set. So it has
no "RESULT()" - section.
I noticed that it seems to return the results of the called sproc (the inner
one). Does the outer sproc try to return all result sets that might occur
while its processing? Other way round: Does it return as much result sets as
much select statements or calls it processes?

Thank you for your ideas!

Uwe


0
Uwe
6/25/2004 12:24:42 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

2 Replies
2200 Views

Similar Articles

[PageSpeed] 24

The following should work, although I didn't check it for syntax, but you
should get the idea.

create Procedure OuterProc ( in as_bzp varchar(128) )
begin
  declare myCur cursor for call sp_breakstring( as_bzp, ',');
  declare c1 varchar(128);
  open myCur;
  fetch first myCur into c1;
  while ( sqlcode == 0 ) loop
    // do whatever you want with c1
    fetch next myCur into c1;
  end loop;
  close myCur;
end;

-- 
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
   -> Choose SQL Anywhere Studio
   -> Set "Platform Preview" and "Time Frame" to ALL

"Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
news:40dc1a14$1@forums-2-dub...
> Hi Group!
>
> I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL.
>
> I have a sproc which breaks a given string-parameter into pieces. It's
> result set contains the pieces, e.g. last line of sproc: "select item from
> mytable;".
> This sproc works fine.
>
> Now I need to call this procedure from another procedure which has a
> variable result set.
> I tried the following:
>
> <snip...
> set ll_retval = 0;
> call sp_breakstring(as_bzp,',');
> BreakLoop:
> while ll_retval <> 100 and not ll_retval < 0 loop
> set li_zaehler = li_zaehler + 1;
> fetch next sp_breakstring into ls_tmp;
> set ll_retval = sqlcode;
> If not ll_retval < 0 and not ll_retval = 100 then
> ---snip>
>
> That did not work with the message "cursor not open", so I tried to
prepare
> the sproc and declare a cursor for it. But it didn't work either.
>
> I searched the online help but wasn't able to find an example how to get
the
> result sets from a sproc within a sproc.
> Can someone help me here?
>
> Another question:
> I need the topmost (outer) sproc to return a variable result set. So it
has
> no "RESULT()" - section.
> I noticed that it seems to return the results of the called sproc (the
inner
> one). Does the outer sproc try to return all result sets that might occur
> while its processing? Other way round: Does it return as much result sets
as
> much select statements or calls it processes?
>
> Thank you for your ideas!
>
> Uwe
>
>


0
Reg
6/25/2004 1:05:35 PM
Reg,

it's working like a champ!

Thanks a lot!
Uwe

"Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com> schrieb im Newsbeitrag
news:40dc231f@forums-1-dub...
> The following should work, although I didn't check it for syntax, but you
> should get the idea.
>
> create Procedure OuterProc ( in as_bzp varchar(128) )
> begin
>   declare myCur cursor for call sp_breakstring( as_bzp, ',');
>   declare c1 varchar(128);
>   open myCur;
>   fetch first myCur into c1;
>   while ( sqlcode == 0 ) loop
>     // do whatever you want with c1
>     fetch next myCur into c1;
>   end loop;
>   close myCur;
> end;
>
> -- 
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
>    -> Choose SQL Anywhere Studio
>    -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
> news:40dc1a14$1@forums-2-dub...
> > Hi Group!
> >
> > I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL.
> >
> > I have a sproc which breaks a given string-parameter into pieces. It's
> > result set contains the pieces, e.g. last line of sproc: "select item
from
> > mytable;".
> > This sproc works fine.
> >
> > Now I need to call this procedure from another procedure which has a
> > variable result set.
> > I tried the following:
> >
> > <snip...
> > set ll_retval = 0;
> > call sp_breakstring(as_bzp,',');
> > BreakLoop:
> > while ll_retval <> 100 and not ll_retval < 0 loop
> > set li_zaehler = li_zaehler + 1;
> > fetch next sp_breakstring into ls_tmp;
> > set ll_retval = sqlcode;
> > If not ll_retval < 0 and not ll_retval = 100 then
> > ---snip>
> >
> > That did not work with the message "cursor not open", so I tried to
> prepare
> > the sproc and declare a cursor for it. But it didn't work either.
> >
> > I searched the online help but wasn't able to find an example how to get
> the
> > result sets from a sproc within a sproc.
> > Can someone help me here?
> >
> > Another question:
> > I need the topmost (outer) sproc to return a variable result set. So it
> has
> > no "RESULT()" - section.
> > I noticed that it seems to return the results of the called sproc (the
> inner
> > one). Does the outer sproc try to return all result sets that might
occur
> > while its processing? Other way round: Does it return as much result
sets
> as
> > much select statements or calls it processes?
> >
> > Thank you for your ideas!
> >
> > Uwe
> >
> >
>
>


0
Uwe
6/25/2004 1:13:36 PM
Reply:

Similar Artilces:

Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')  but I also have a second stored procedure that needs to make reference to&n...

Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...

how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...

Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...

Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...

Calling Stored Procedure fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames I believe you want to use 'EXEC' abad...

Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....

Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...

Create a Stored procedure and call that stored procedure in Script
How can i create a stored procedure in the database and call that in my Sychronization Script ? Is there a way to call the stored procedure that is located in Consolidated Script and Remote database seperately ? Please help. The custdb sample that shipped with ASA does this all the time. CREATE PROCEDURE ULResolveOrderConflict() BEGIN -- approval overrides denial IF 'Approved' = (SELECT status FROM ULNewOrder) THEN UPDATE ULOrder o SET o.status = n.status, o.notes = n.notes FROM ULNewOrder n WHERE o.order_id = n.order_id; END IF; ...

calling a store procedure inside another stored procedure
CREATE PROCEDURE `sp_test`.`proc_tot_record`(FK_sites_COL INT ) BEGIN SELECT COUNT(*) FROM tbl_virtual_drive WHERE tbl_virtual_drive.FK_sites = FK_sites_COL; END CREATE PROCEDURE `test`.`paging_with_param`(FK_Sites_COL INT, Start_Limit_COL INT, End_Limit_COL INT) BEGINPREPARE STMT FROM "SELECTtbl_virtual_drive.PK_virtual_drive,tbl_virtual_drive.file_name,tbl_virtual_drive.file_extension,tbl_virtual_drive.FK_sitesFROMtbl_virtual_driveWHEREtbl_virtual_drive.FK_sites = ?ORDER BYtbl_virtual_drive.file_nameLIMIT ?,?";EXECUTE STMT USING @FK_Sites, @Start_Limit, @End_Limit...

calling another stored procedure within a stored procedure?
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question .. 1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@hotmail.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why? 2) I try to move a file .. how do I find out if the moving is completed successfully? -- MOVE FILES  SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD   Thanks for your reply in advance. 1. I hav...

Is there a utility to see which stored procedures call another stored procedure?
I'm looking for a dependency diagram / pretty print utility for stored procedures / functions in SQL Anywhere. Thank You in advance I don't know of any pretty print utility, but the built in function traceback may be of help to you. Jason Hinsperger International and Sustaining Engineering Adaptive Server Anywhere Chris McCormack wrote: > > I'm looking for a dependency diagram / pretty print utility for stored > procedures / functions in SQL Anywhere. > > Thank You in advance ...

call one stored procedure from inside another stored procedure
How do you call one stored procedure from inside another stored procedure and return a value?Does this hinder performance?Which is better, do it as above, or have a .NET method calling the stored procedure A, getting the value it returns and then passing it to stored procedure B?  You can use "EXEC your stored procedure name" in a stored procedure to call another stored procedure and this way will be faster than call A and then B from web app.This posting is provided "AS IS" with no warranties, and confers no rights. Cheers JimmyM, thanks for that. How can I pass parameters from o...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

Web resources about - calling a stored procedure from a stored procedure - sybase.sqlanywhere.general

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as ​tubal ligation would violate its religious freedom

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

Resources last updated: 2/9/2016 2:15:08 AM