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.
0
Suresh
12/13/2004 5:27:40 AM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

4 Replies
1061 Views

Similar Articles

[PageSpeed] 33

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.g. use the command "dbeng9 -v" for ASA 9.x)

Can you show us the code of what you have tried?

greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
0
Greg
12/13/2004 8:34:27 AM
Calling Procedure
------------------
ALTER PROCEDURE "DBA"."wf_expand_tests"(in labid
decimal(14),in labtype char(1),in testid char(10),in regord
integer,in testord integer)
begin
  declare cursor_not_open exception for sqlstate value
'02000';
  declare get_sub_group_testid dynamic scroll cursor for
select t_group_test_master.sub_test_id,
     
t_test_master.interface_test_id,t_group_test_master.order_no
from t_group_test_master,t_test_master where
      (t_test_master.test_id =
t_group_test_master.sub_test_id) and
      (t_group_test_master.test_id = testid) order by
order_no;
  declare subtestid char(10);
  declare ord_no integer;
  declare int_test_id char(10);
  declare normalrange char(300);
  open get_sub_group_testid;
  set ord_no=0;
  select max(test_ord) into testord from t_patient_details
where lab_id =
    labid and lab_type = labtype;
  set testord=testord+1;
  SUBTESTLOOP: loop
    fetch next get_sub_group_testid into
subtestid,int_test_id,ord_no;

    if sqlstate = cursor_not_open then
      leave SUBTESTLOOP
    else
      normalrange = call
sp_multiple_refrange(labid,labtype,subtestid);
      insert into t_patient_details(
lab_id,lab_type,test_id,normal_range,result,
       
sample_quantity,instruction,kit_no,test_charges,test_entery_status
,staff_no,
       
interface_test_no,interface_batch_no,test_selection,group_expand
,group_test,
       
list_flag,print_status,interface_test_id,seq_num,reg_ord,test_ord)
values( labid,labtype,
       
subtestid,normalrange,'0','0','0',0,0,'N',0,0,0,'Y','N',testid
,'N','N',
        int_test_id,ord_no,regord,testord)
    end if
  end loop SUBTESTLOOP;
  close get_sub_group_testid;
  update t_patient_details set test_selection =
'N',group_expand = 'Y' where lab_id =
    labid and lab_type = labtype and test_id = testid
end

----------
Called Procedure

ALTER PROCEDURE "DBA"."sp_multiple_refrange"( in labid
decimal(14),in labtype char(1),in testid char(10),out
multi_refrange varchar(1000) )
BEGIN
  -- General Variable Declarations
  declare is_mrefrange char(1);
  declare is_agebased char(1);
  declare is_sexbased char(1);
  declare ageindays integer;
  declare pat_sex char(1);
  //  select labid;
  //select labtype;
  //select testid;
  -- Check if the test has multiple reference range
  select is_mref_range into is_mrefrange from t_test_master
    where test_id=testid;
  //  select is_agebased;
  -- If test has multiple reference range
  if is_mrefrange='Y' then
    -- Get the age indays from t_patient_master for that
patient
    select age_indays,sex into ageindays,pat_sex from
t_patient_master
      where lab_id=labid and lab_type=labtype;
    //select ageindays;
    //select pat_sex;
    -- From t_test_master, find out if the reference range
is age based or sex based or both
    select is_age_based,is_sex_based into
is_agebased,is_sexbased from t_test_master
      where test_id=testid;
    // select is_agebased;
    -- In case it is age based, then include age parameter
in where clause
    -- while querying on t_multiple_refrange
    if is_agebased='Y' and is_sexbased='N' then
      select mnrange into multi_refrange from
t_multiple_refrange
        where test_id=testid
        and ageindays between low_age_indays and
high_age_indays
    end if
    ; -- In case it is sex based, then include sex parameter
in where clause
    -- while querying on t_multiple_refrange
    if is_agebased='N' and is_sexbased='Y' then
      select mnrange into multi_refrange from
t_multiple_refrange
        where test_id=testid
        and sex=pat_sex
    end if
    ; -- In case it is age and sex based, then include both
age and sex parameters in where clause
    -- while querying on t_multiple_refrange
    if is_agebased='Y' and is_sexbased='Y' then
      select mnrange into multi_refrange from
t_multiple_refrange
        where test_id=testid
        and sex=pat_sex
        and ageindays between low_age_indays and
high_age_indays
    end if
    ;
  else
       select normal_range into multi_refrange from
t_test_master
        where test_id=testid;
  end if;
  select multi_refrange;
END

> 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.g. use the command "dbeng9 -v" for
> ASA 9.x)
>
> Can you show us the code of what you have tried?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
0
Suresh
12/23/2004 3:23:34 AM
Suresh wrote:
> ALTER PROCEDURE "DBA"."wf_expand_tests"(in labid
> decimal(14),in labtype char(1),in testid char(10),in regord
> integer,in testord integer)
> begin
> [...]
>      normalrange = call
> sp_multiple_refrange(labid,labtype,subtestid);

This is not normal calling conventions for a stored procedure.  If you 
want a value returned, you either pass in a variable to the OUT 
arguement of the stored proc:

     call sp_multiple_refrange(labid,labtype,subtestid, normalrange);

or you change the stored procedure into a *function*.  A stored 
procedure can return a *result set*, but not a single value the way you 
are trying to call it [at least, I've never seen it work this way].

Hope this helps,
greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
0
Greg
12/28/2004 4:42:05 PM
Greg Fenton wrote:
> 
> This is not normal calling conventions for a stored procedure. 

A Jedi Birdie (named Breck) whispered in my email pointing out that, 
once again, I am wrong [you think I'd get used to it after a while :-) ]

The syntax:

     [variable =] call procedure-name( [...] )

is indeed valid and documented (from the ASA 9.0.2 online docs):

   ASA SQL Reference
     SQL Statements
       CALL statement

However, as the following section points out, you likely need to return 
a single value with the RETURN statement, not with the SELECT statement 
(which would return a result set):

   ASA SQL User's Guide
     Using Procedures, Triggers, and Batches
       Returning results from procedures


So you should either use RETURN in your SP_B or you should convert it to 
a FUNCTION.

greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
0
Greg
12/28/2004 5:45:11 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...

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 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...

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 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...

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...

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...

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_breakstrin...

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...

Re: How a stored procedure call another stored procedure and then process the results of latter
How a stored procedure can call another stored procedure and process the result set returned from the second stored procedure in Sybase ? Thanks, This is typically done by having the first stored procedure create a temporary table that the second stored procedure populates before it returns. -bret Sybase_User:_henrydu@hotmail.com wrote: > How a stored procedure can call another stored procedure and process > the result set returned from the second stored procedure in Sybase ? > > Thanks, Hi bret, As you said, first stored procedure create a temporar...

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...

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; ...

Web resources about - Call as stored procedure from another stored procedure - sybase.sqlanywhere.general

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

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 ...

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 ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

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. ...

New Defence Procurement Procedure to push 'Make in India' initiative
New Defence Procurement Procedure to push 'Make in India' initiative - Under the new DPP, a new category called the 'IDDM' or 'Indigenously Designed, ...

Resources last updated: 1/13/2016 3:24:08 PM