Running a stored procedure within a stored procedure

I have a stored procedure (A), which I need to call from
another stored procedure (B). The data pulled from (B) is
then inserted into a temporary table.  The problem I am
having is the stored procedure is in a loop, and it only
runs once.  I need it to run each time the statement loops.

For example:
In the cursor I have, there are 3 rows, so the statement
should return 3 rows.  However, only 1 row is returned, it
looks like the stored procedure is only one once.  What do I
have to do to get it to loop??

DECLARE dCURSOR CURSOR FOR
select ID, value1, value2 from table;

OPEN dCURSOR;
DataCursorLoop:
LOOP
    FETCH NEXT dCURSOR INTO @ID, @value1, @value2;
        IF SQLSTATE = ErrNotFound
   	          THEN LEAVE DataCursorLoop;
        END IF;
        CALL  "sp_A" (@ID);
        insert into tempTable values (@ID, @value1,
@value2);
END LOOP DataCursorLoop;
CLOSE dCURSOR;

What am I doing wrong?
Thanks!
0
khoke
5/6/2009 2:53:11 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

5 Replies
801 Views

Similar Articles

[PageSpeed] 36

What are you doing in sp_A? I guess there must be something that closes the 
cursor.  Are you doing a commit?

Arthur


<khoke> wrote in message news:4a01a457.58c8.1681692777@sybase.com...
>I have a stored procedure (A), which I need to call from
> another stored procedure (B). The data pulled from (B) is
> then inserted into a temporary table.  The problem I am
> having is the stored procedure is in a loop, and it only
> runs once.  I need it to run each time the statement loops.
>
> For example:
> In the cursor I have, there are 3 rows, so the statement
> should return 3 rows.  However, only 1 row is returned, it
> looks like the stored procedure is only one once.  What do I
> have to do to get it to loop??
>
> DECLARE dCURSOR CURSOR FOR
> select ID, value1, value2 from table;
>
> OPEN dCURSOR;
> DataCursorLoop:
> LOOP
>    FETCH NEXT dCURSOR INTO @ID, @value1, @value2;
>        IF SQLSTATE = ErrNotFound
>             THEN LEAVE DataCursorLoop;
>        END IF;
>        CALL  "sp_A" (@ID);
>        insert into tempTable values (@ID, @value1,
> @value2);
> END LOOP DataCursorLoop;
> CLOSE dCURSOR;
>
> What am I doing wrong?
> Thanks! 


0
Arthur
5/6/2009 3:38:22 PM
I am closing the cursor, and then selecting from the temp
table.
No, I am not doing a commit, where would that go?

The sp_A is inserting data into a temp table, and then that
temp table is called by the 2nd stored procedure.

> What are you doing in sp_A? I guess there must be
> something that closes the  cursor.  Are you doing a
> commit?
>
> Arthur
>
>
> <khoke> wrote in message
> news:4a01a457.58c8.1681692777@sybase.com... >I have a
> > stored procedure (A), which I need to call from another
> > stored procedure (B). The data pulled from (B) is then
> > inserted into a temporary table.  The problem I am
> > having is the stored procedure is in a loop, and it only
> runs once.  I need it to run each time the statement
> loops. >
> > For example:
> > In the cursor I have, there are 3 rows, so the statement
> > should return 3 rows.  However, only 1 row is returned,
> > it looks like the stored procedure is only one once.
> > What do I have to do to get it to loop??
> >
> > DECLARE dCURSOR CURSOR FOR
> > select ID, value1, value2 from table;
> >
> > OPEN dCURSOR;
> > DataCursorLoop:
> > LOOP
> >    FETCH NEXT dCURSOR INTO @ID, @value1, @value2;
> >        IF SQLSTATE = ErrNotFound
> >             THEN LEAVE DataCursorLoop;
> >        END IF;
> >        CALL  "sp_A" (@ID);
> >        insert into tempTable values (@ID, @value1,
> > @value2);
> > END LOOP DataCursorLoop;
> > CLOSE dCURSOR;
> >
> > What am I doing wrong?
> > Thanks!
>
>
0
khoke
5/6/2009 4:20:44 PM
If there is a commit, the cursor will close (by default).

Use the WITH HOLD clause on the OPEN <cursor>. It that gets past the 
issue, you have a COMMIT occuring - either explicitly or as a side 
effect. We would need to see the exact code for sp_A to point you in the 
right directly. Please note, many DDL statements have COMMIT as a side 
effect.

-chris


khoke wrote:
> I am closing the cursor, and then selecting from the temp
> table.
> No, I am not doing a commit, where would that go?
> 
> The sp_A is inserting data into a temp table, and then that
> temp table is called by the 2nd stored procedure.
> 
>> What are you doing in sp_A? I guess there must be
>> something that closes the  cursor.  Are you doing a
>> commit?
>>
>> Arthur
>>
>>
>> <khoke> wrote in message
>> news:4a01a457.58c8.1681692777@sybase.com... >I have a
>>> stored procedure (A), which I need to call from another
>>> stored procedure (B). The data pulled from (B) is then
>>> inserted into a temporary table.  The problem I am
>>> having is the stored procedure is in a loop, and it only
>> runs once.  I need it to run each time the statement
>> loops. >
>>> For example:
>>> In the cursor I have, there are 3 rows, so the statement
>>> should return 3 rows.  However, only 1 row is returned,
>>> it looks like the stored procedure is only one once.
>>> What do I have to do to get it to loop??
>>>
>>> DECLARE dCURSOR CURSOR FOR
>>> select ID, value1, value2 from table;
>>>
>>> OPEN dCURSOR;
>>> DataCursorLoop:
>>> LOOP
>>>    FETCH NEXT dCURSOR INTO @ID, @value1, @value2;
>>>        IF SQLSTATE = ErrNotFound
>>>             THEN LEAVE DataCursorLoop;
>>>        END IF;
>>>        CALL  "sp_A" (@ID);
>>>        insert into tempTable values (@ID, @value1,
>>> @value2);
>>> END LOOP DataCursorLoop;
>>> CLOSE dCURSOR;
>>>
>>> What am I doing wrong?
>>> Thanks!
>>
0
Chris
5/6/2009 4:46:31 PM
One alternative might be to invoke the stored procedure in the FROM clause, 
and alias it as a derived table.

That would save the whole business of working with cursors, inserting into 
temp tables, and so on.

-- 
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

<khoke> wrote in message news:4a01b8dc.5a9f.1681692777@sybase.com...
>I am closing the cursor, and then selecting from the temp
> table.
> No, I am not doing a commit, where would that go?
>
> The sp_A is inserting data into a temp table, and then that
> temp table is called by the 2nd stored procedure.
>
>> What are you doing in sp_A? I guess there must be
>> something that closes the  cursor.  Are you doing a
>> commit?
>>
>> Arthur
>>
>>
>> <khoke> wrote in message
>> news:4a01a457.58c8.1681692777@sybase.com... >I have a
>> > stored procedure (A), which I need to call from another
>> > stored procedure (B). The data pulled from (B) is then
>> > inserted into a temporary table.  The problem I am
>> > having is the stored procedure is in a loop, and it only
>> runs once.  I need it to run each time the statement
>> loops. >
>> > For example:
>> > In the cursor I have, there are 3 rows, so the statement
>> > should return 3 rows.  However, only 1 row is returned,
>> > it looks like the stored procedure is only one once.
>> > What do I have to do to get it to loop??
>> >
>> > DECLARE dCURSOR CURSOR FOR
>> > select ID, value1, value2 from table;
>> >
>> > OPEN dCURSOR;
>> > DataCursorLoop:
>> > LOOP
>> >    FETCH NEXT dCURSOR INTO @ID, @value1, @value2;
>> >        IF SQLSTATE = ErrNotFound
>> >             THEN LEAVE DataCursorLoop;
>> >        END IF;
>> >        CALL  "sp_A" (@ID);
>> >        insert into tempTable values (@ID, @value1,
>> > @value2);
>> > END LOOP DataCursorLoop;
>> > CLOSE dCURSOR;
>> >
>> > What am I doing wrong?
>> > Thanks!
>>
>> 


0
Paul
5/6/2009 6:24:52 PM
Thank you so much for your help.

A little background on the issue.  Users have items to enter
data into, FormA.  Rather than store all the responses to
FormA in a table, each individual answer is stored sperately
in is own row.
For example-

ID  FormID  Answer  EmpID
--  ------  ------  -----
1   FormA   x       0001
2   FormA   y       0001
3   FormA   z       0001

I need to pull these values into one record, for reporting,
therefore, sp_A takes each individual record, and adds them
to a field, so the entire form is returned as one record.
Form, Answer1, Answer2, Answer3 EmpID
----  -------  -------  ------- -----
FormA    x       y        z     0001

The problem is that one person may enter multiple Forms
(FormA), so I need to pull all those rows for one person, so
I can report off them.

sp_A pulls all the FormA responses into 1 record per form.

ALTER PROCEDURE "reportwriter"."sp_A"( IN sFormID char(15))

RESULT (
formID VARCHAR(15), empID VARCHAR(15), value1 VARCHAR(15),
value2  VARCHAR(15), value3 VARCHAR(15)
)
BEGIN
DECLARE @ElementDescription CHAR(100);
DECLARE @ElementValue VARCHAR(5000);

DECLARE @formID VARCHAR(15);
DECALRE @empID  VARCHAR(15);
DECLARE @value1 VARCHAR(15);
DECLARE @value2 VARCHAR(15);
DECLARE @value3 VARCHAR(15);
DECLARE ErrNotFound  EXCEPTION FOR SQLSTATE '02000';

DECLARE LOCAL TEMPORARY TABLE tempTableA
(
formID VARCHAR(15),
empID  VARCHAR(15);
value1 VARCHAR(15),
value2 VARCHAR(15),
value3 VARCHAR(15),
);

DECLARE DATACURSOR CURSOR FOR
SELECT formID, empID FROM reportwriter.table1 where formID =
sFormID;

DECLARE DATACURSOR_ALL CURSOR FOR
SELECT answerDescription, answer FROM reportwriter.table2
where formID = sFormID;

OPEN DATACURSOR;
FETCH NEXT DATACURSOR INTO @formID, @emp;
CLOSE DATACURSOR;

OPEN DATACURSOR_ALL;
DataCursorLoop:
Loop
FETCH NEXT DATACURSOR_ALL INTO @ElementDescription,
@ElementValue;
        IF SQLSTATE = ErrNotFound
   	          THEN LEAVE DataCursorLoop;
        END IF;

        If @ElementDescription = Answer1' THEN
            set @value1= @ElementValue;
        END IF;
        If @ElementDescription = 'Answer2' THEN
            set @value2 = @ElementValue;
        END IF;
        If @ElementDescription = Answer3' THEN
            set @value3= @ElementValue;
        END IF;
END LOOP DataCursorLoop;
CLOSE DATACURSOR_Al;
-- Output results
INSERT INTO tempTableA
        VALUES
        (@formID, @empID, @value1, @value2, @value3);

Select * From tempTableA
END


Now, I need to compile all the results for this stored
procedure by empID

ALTER PROCEDURE "reportwriter"."sp_B"( IN EmpID char(15))

RESULT (
formID VARCHAR(15), empID VARCHAR(15), value1 VARCHAR(15),
value2  VARCHAR(15), value3 VARCHAR(15)
)
BEGIN

DECLARE @formID VARCHAR(15);
DECALRE @empID  VARCHAR(15);
DECLARE @value1 VARCHAR(15);
DECLARE @value2 VARCHAR(15);
DECLARE @value3 VARCHAR(15);
DECLARE ErrNotFound  EXCEPTION FOR SQLSTATE '02000';

DECLARE LOCAL TEMPORARY TABLE tempTableB
(
formID VARCHAR(15),
empID  VARCHAR(15);
value1 VARCHAR(15),
value2 VARCHAR(15),
value3 VARCHAR(15),
);

DECLARE dCURSOR CURSOR FOR
select formID, empID, value1, value2 from table;

OPEN dCURSOR;
DataCursorLoop:
LOOP
FETCH NEXT dCURSOR INTO @formID, @empID, @value1, @value2,
@value3;
  IF SQLSTATE = ErrNotFound
    THEN LEAVE DataCursorLoop;
  END IF;

  CALL  "sp_A" (@formID);

  insert into tempTable values (@formID, @empID, @value1,
@value2, @value3);
END LOOP DataCursorLoop;
CLOSE dCURSOR;

-- Output results
INSERT INTO tempTableB
        VALUES
        (@formID, @empID, @value1, @value2, @value3);

Select * From tempTableB
END

This will give me all the form answers in one record by
formID for one employee, so I can use this data for
reporting.

The problem is that when I call the sp_A, only one record is
returned, even though multiple records should be returned.
Thank you so much for all your help!!!
0
khoke
5/7/2009 12:39:43 PM
Reply:

Similar Artilces:

Running stored procedure within a stored procedure
I have a custom built users table for storing some values and I am also utilizing the aspnet_Users table. I want to delete a user from my users tables then execute the aspnet_Users_DeleteUser sproc and pass into the stored procedure the username of the user to delete because the DeleteUser method requires this. When I execute the command from within my asp.net web application I get the exception below. Both values are being obtained from the asp.net application and are represented in my DAL that is also below. Any thoughts as to why I am receiving this exception? Thanks. Procedure or functi...

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

stored procedure. The c# code run stored procedure.
 CREATE PROCEDURE GetThreadMessages @iThreadID int AS SELECT message_id, thread_id, user_id, first_names, last_name, email, subject, body, date_submitted, category_name, category_id, last_edited FROM message_view WHERE thread_id = @iThreadID ORDER BY date_submitted asc For update ---------------------------------------------------------------------------------------- CREATE PROCEDURE UpdateThreadMessages @iThreadID int @name varchar(50) AS UPDATE message_view SET first_names = @name FROM message_view ...

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

get result set from stored procedure within a stored procedure
Dear All, Would someone tell me how to get result set from stored procedure within a stored procedure? I am testing ASE 11.0.3.3 on linux. In MSSQL Server 6.5 or above, the insert statement can insert result set from stored procedures, i.e. insert into #temp_table execute my_store_proc @val1, @val2.. In SQLAnywhere 5.0 or above, cursor can be declared on procedures, i.e. declare my_cur cursor for call my_store_proce(@val1, ...); Once cursor is declare, rows can be fetched into a temp table. But these two approaches do not work in 11.0.3.3. You have to write the ...

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

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

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

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

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

Web resources about - Running a stored procedure within a 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 ...

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

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

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Robust Growth In Procedures, Beneficial Product Mix Drive Intuitive Surgical's Strong Q3 Performance
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 12/12/2015 3:15:04 AM