Call stored proc inside another stored proc

[mandible]

I'm trying to call one stored procedure inside another.
I was wondering if this is possible
Some ideas I was toying with is putting the first stored procedure
inside of a temp table but haven't been able to get this idea to work.

[jefftyzzer]

Calling one SP from another is absolutely possible. You should have to
do nothing more than issue a CALL statement within the main SP, e.g.,

CREATE PROCEDURE TEST()
....
BEGIN
CALL 2NDSP(P_PARM1);--
END;

I'm, not sure what you mean by "putting the first stored procedure
*inside* of a temp table," but hopefully the more straightforward CALL
approach described above will obviate any need for what sounds like a
work-around. Also, be aware that if you intend to have the nested SP
return a result set to the calling SP, don't forget to declare the
nested SP as WITH RETURN TO CALLER.

Regards,

--Jeff

mandible wrote:

I'm trying to call one stored procedure inside another.
I was wondering if this is possible
Some ideas I was toying with is putting the first stored procedure
inside of a temp table but haven't been able to get this idea to work.

[mandible]

Okay I'll try and be a little more descriptive
I have a huge stored procedure that does all of the selects and
calculations for our product but it's big and over kill for smaller
tasks.

Now this large stored proc is actually filled with 3 or 4 temporary
table sections.
I want to be able to take this sections and create there own stored
procedures based on them, but I dont' want to have to maintain the same
code in 4 different places.
So what I want to do is create 4 stored procedures so this is how it
would go

StoredProc1 -handles what was in temporary table 1
StoredProc2 -calls storedproc1 and based on the data from storedproc1
returns what was the second temporary table
StoredProc3 -calls storedproc2 and based on the data from storedproc2
returns what was the third temporary table

In this way if I need to fix something in stored proc 1 then it will
propagate to the other stored procedures quite nicely.
Is this possible?
On Oct 17, 2:31 pm, "jefftyzzer" <[e-mail]:

Calling one SP from another is absolutely possible. You should have to
do nothing more than issue a CALL statement within the main SP, e.g.,

CREATE PROCEDURE TEST()
...
BEGIN
CALL 2NDSP(P_PARM1);--
END;

I'm, not sure what you mean by "putting the first stored procedure
*inside* of a temp table," but hopefully the more straightforward CALL
approach described above will obviate any need for what sounds like a
work-around. Also, be aware that if you intend to have the nested SP
return a result set to the calling SP, don't forget to declare the
nested SP as WITH RETURN TO CALLER.

Regards,

--Jeff

mandible wrote:

I'm trying to call one stored procedure inside another.
I was wondering if this is possible
Some ideas I was toying with is putting the first stored procedure
inside of a temp table but haven't been able to get this idea to work.

[jefftyzzer]

Yes, this is possible, and I certainly commend your goal of
modularizing your code :-).

One thing I'd recommend out of the gate is that you pick up a copy of
Janmohamed, et. al.'s book _DB2 SQL PL Essential Guide for DB2 UDB..._.
The authors describe exactly what you're looking for in chapters 8 and
10, which I'm essentially borrowing from in this reply.

Since declared global temporary tables (DGTTs) are private to your
session and go away when the session does, you are able to create a
DGTT in one SP and use it in another, as long as both SPs were called
in the same session. Between this capability and the capability to nest
SP calls *and* pass result sets between nested SPs, you should be able
to do what you want. But since you can refer to DGTTs at will once
decalred, you hopefully won't have to do too much result set passing.

HTH,

--Jeff

mandible wrote:

Okay I'll try and be a little more descriptive
I have a huge stored procedure that does all of the selects and
calculations for our product but it's big and over kill for smaller
tasks.

Now this large stored proc is actually filled with 3 or 4 temporary
table sections.
I want to be able to take this sections and create there own stored
procedures based on them, but I dont' want to have to maintain the same
code in 4 different places.
So what I want to do is create 4 stored procedures so this is how it
would go

StoredProc1 -handles what was in temporary table 1
StoredProc2 -calls storedproc1 and based on the data from storedproc1
returns what was the second temporary table
StoredProc3 -calls storedproc2 and based on the data from storedproc2
returns what was the third temporary table

In this way if I need to fix something in stored proc 1 then it will
propagate to the other stored procedures quite nicely.
Is this possible?
On Oct 17, 2:31 pm, "jefftyzzer" <[e-mail]:

Calling one SP from another is absolutely possible. You should have to
do nothing more than issue a CALL statement within the main SP, e.g.,

CREATE PROCEDURE TEST()
...
BEGIN
CALL 2NDSP(P_PARM1);--
END;

I'm, not sure what you mean by "putting the first stored procedure
*inside* of a temp table," but hopefully the more straightforward CALL
approach described above will obviate any need for what sounds like a
work-around. Also, be aware that if you intend to have the nested SP
return a result set to the calling SP, don't forget to declare the
nested SP as WITH RETURN TO CALLER.

Regards,

--Jeff

mandible wrote:

I'm trying to call one stored procedure inside another.
I was wondering if this is possible
Some ideas I was toying with is putting the first stored procedure
inside of a temp table but haven't been able to get this idea to work.