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. 3 15778
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.
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 " <jefftyz...@sbc global.netwrote :
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.
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 " <jefftyz...@sbc global.netwrote :
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mariusz |
last post by:
I want to write function to call another function which name is
parameter to first function. Other parameters should be passed to
called function.
If I call it function('f1',10) it should call f1(10). If I call it
function('f2',5) it should call f2(5).
So far i tried something like
CREATE FUNCTION . (@f varchar(50),@m money)
RETURNS varchar(50) AS
|
by: Jean-Marc Blaise |
last post by:
Dear all,
I have simulated the windows MULTI application with a java program calling
the SQLTP1DL proc referenced as DB2DARI application, on Linux Intel or
ZLinux. If the proc is NOT FENCED, there is no pb and the program works
fine. If either the proc is FENCED, or FENCED THREADSAFE, I get a SQL1042C,
or the instance crashes on ZLinux (V8.1 FP4).
The proc does a return(SQLZ_HOLD_PROC):
$ /opt/IBMJava2-131/bin/java sqltp1ri 1
|
by: Dave Sisk |
last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined
a SQL stored proc, and the trigger is set to call this SP. I've posted the
simplified source below. I can manually call the stored proc, and the
external trigger is created without any errors. However, when I do...
|
by: Newbie |
last post by:
how can i call an oracle function to get data without using a select
statement or stored procedures?
given a project_no, i need to call the function:
ops$sqltime.pa_new_job_no_fn
which will return the next job_no
thanks in advance.
|
by: Mo |
last post by:
Hi,
I have a webform which has vb.net code behind it and I would like it to
submit the entries in the fields into a sql server db using a stored
procedure. I have a central .vb file in my application that has all the
stored proc calls in it. in the Submit_Click Sub in my form.vb file, I
reference the function in my central .vb file which calls the stored proc.
The problem that I am having is that when I submit my form, it just...
| |
by: mooreit |
last post by:
The purpose for my questions is accessing these technologies from
applications. I develop both applications and databases. Working with
Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test
Environments.
What is the purpose of a view if I can just copy the vode from a view
and put it into a stored procedure?
Should I be accessing views from stored procedures?
|
by: Jason Huang |
last post by:
Hi,
In my C# Windows Form application project, I have done all queries on my
codes.
Now I wanna try using the Stored Procedure.
But I am not clear about why using the stored procedure.
Would some one give me some advice?
Thanks for help.
|
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello Everyone,
I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is returning
some value. I cannot see that value in my .net code
Below is my .net code
OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number);
mbrid.Direction = ParameterDirection.Output;
|
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello Everyone,
I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is returning
some value. I cannot see that value in my .net code
Below is my .net code
OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number);
mbrid.Direction = ParameterDirection.Output;
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |