Wrapping procedure calls in one main procedure
I have four working stored procedures (DB2), each accepting their own parameters, but a lot of those params are the same.
I'm trying to create a larger wrapper procedure that will basically just call each in order. There are about 10 outside values coming into the wrapper procedure as it's own parameters.
Within the body, where I call all 4 child parameters, I will be using a mixture of outside params as well as a main ID param that is derived from the first call.
How can I properly wrap these four procedure calls using the outside params as well as the captured/derived parameter from my first call?
Parameters:
FIRST_NAME
LAST_NAME
PROFILE_IMAGE
CODE
START_DATE
EXPIRE_DATE
PRIORITY
CUST_NUMBER
CONTACT_TYPE
CONTACT_VALUE
CALL FIRST_PROC(FIRST_NAME,LAST_NAME,PROFILE_IMAGE)
--This returns ```FIRST_PROC_ID```
CALL SECOND_PROC(FIRST_PROC_ID, CODE,START_DATE,EXPIRE_DATE,PRIORITY)
CALL THIRD_PROC(FIRST_PROC_ID,CODE,CUST_NUMBER,START_DATE,EXPIRE_DATE,PRIORITY)
CALL FOURTH_PROC(FIRST_PROC_ID,CONTACT_TYPE,CONTACT_VALUE,START_DATE,EXPIRE_DATE)
sql stored-procedures db2
asked Dec 27 '18 at 20:05
Tom N.
1,215414
add a comment |
I have four working stored procedures (DB2), each accepting their own parameters, but a lot of those params are the same.
I'm trying to create a larger wrapper procedure that will basically just call each in order. There are about 10 outside values coming into the wrapper procedure as it's own parameters.
Within the body, where I call all 4 child parameters, I will be using a mixture of outside params as well as a main ID param that is derived from the first call.
How can I properly wrap these four procedure calls using the outside params as well as the captured/derived parameter from my first call?
Parameters:
FIRST_NAME
LAST_NAME
PROFILE_IMAGE
CODE
START_DATE
EXPIRE_DATE
PRIORITY
CUST_NUMBER
CONTACT_TYPE
CONTACT_VALUE
CALL FIRST_PROC(FIRST_NAME,LAST_NAME,PROFILE_IMAGE)
--This returns ```FIRST_PROC_ID```
CALL SECOND_PROC(FIRST_PROC_ID, CODE,START_DATE,EXPIRE_DATE,PRIORITY)
CALL THIRD_PROC(FIRST_PROC_ID,CODE,CUST_NUMBER,START_DATE,EXPIRE_DATE,PRIORITY)
CALL FOURTH_PROC(FIRST_PROC_ID,CONTACT_TYPE,CONTACT_VALUE,START_DATE,EXPIRE_DATE)
sql stored-procedures db2
asked Dec 27 '18 at 20:05
Tom N.
1,215414
Pass the supplied parameters into each sproc, and output the result of the first sproc to a variable. Are you asking how to output the result of the first sproc to where you can use it in the other 3? Or are you asking how to write a stored procedure?
– Garrison Becker
Dec 27 '18 at 20:44
I feel comfortable in this situation with writing the proc because I'm basically just calling for procedures in the body of this one with their own parameters. My biggest question is how to capture output of one of the procedures in the body to be used in the others
– Tom N.
Dec 27 '18 at 20:53
add a comment |
I have four working stored procedures (DB2), each accepting their own parameters, but a lot of those params are the same.
I'm trying to create a larger wrapper procedure that will basically just call each in order. There are about 10 outside values coming into the wrapper procedure as it's own parameters.
Within the body, where I call all 4 child parameters, I will be using a mixture of outside params as well as a main ID param that is derived from the first call.
How can I properly wrap these four procedure calls using the outside params as well as the captured/derived parameter from my first call?
Parameters:
FIRST_NAME
LAST_NAME
PROFILE_IMAGE
CODE
START_DATE
EXPIRE_DATE
PRIORITY
CUST_NUMBER
CONTACT_TYPE
CONTACT_VALUE
CALL FIRST_PROC(FIRST_NAME,LAST_NAME,PROFILE_IMAGE)
--This returns ```FIRST_PROC_ID```
CALL SECOND_PROC(FIRST_PROC_ID, CODE,START_DATE,EXPIRE_DATE,PRIORITY)
CALL THIRD_PROC(FIRST_PROC_ID,CODE,CUST_NUMBER,START_DATE,EXPIRE_DATE,PRIORITY)
CALL FOURTH_PROC(FIRST_PROC_ID,CONTACT_TYPE,CONTACT_VALUE,START_DATE,EXPIRE_DATE)
sql stored-procedures db2
asked Dec 27 '18 at 20:05
Tom N.
1,215414
I have four working stored procedures (DB2), each accepting their own parameters, but a lot of those params are the same.
I'm trying to create a larger wrapper procedure that will basically just call each in order. There are about 10 outside values coming into the wrapper procedure as it's own parameters.
Within the body, where I call all 4 child parameters, I will be using a mixture of outside params as well as a main ID param that is derived from the first call.
How can I properly wrap these four procedure calls using the outside params as well as the captured/derived parameter from my first call?
Parameters:
FIRST_NAME
LAST_NAME
PROFILE_IMAGE
CODE
START_DATE
EXPIRE_DATE
PRIORITY
CUST_NUMBER
CONTACT_TYPE
CONTACT_VALUE
CALL FIRST_PROC(FIRST_NAME,LAST_NAME,PROFILE_IMAGE)
--This returns ```FIRST_PROC_ID```
CALL SECOND_PROC(FIRST_PROC_ID, CODE,START_DATE,EXPIRE_DATE,PRIORITY)
CALL THIRD_PROC(FIRST_PROC_ID,CODE,CUST_NUMBER,START_DATE,EXPIRE_DATE,PRIORITY)
CALL FOURTH_PROC(FIRST_PROC_ID,CONTACT_TYPE,CONTACT_VALUE,START_DATE,EXPIRE_DATE)
sql stored-procedures db2
sql stored-procedures db2
asked Dec 27 '18 at 20:05
Tom N.
1,215414
asked Dec 27 '18 at 20:05
Tom N.
1,215414
asked Dec 27 '18 at 20:05
Tom N.
1,215414
asked Dec 27 '18 at 20:05
Tom N.
1,215414
asked Dec 27 '18 at 20:05
Tom N.
1,215414
1,215414
Pass the supplied parameters into each sproc, and output the result of the first sproc to a variable. Are you asking how to output the result of the first sproc to where you can use it in the other 3? Or are you asking how to write a stored procedure?
– Garrison Becker
Dec 27 '18 at 20:44
I feel comfortable in this situation with writing the proc because I'm basically just calling for procedures in the body of this one with their own parameters. My biggest question is how to capture output of one of the procedures in the body to be used in the others
– Tom N.
Dec 27 '18 at 20:53
add a comment |
Pass the supplied parameters into each sproc, and output the result of the first sproc to a variable. Are you asking how to output the result of the first sproc to where you can use it in the other 3? Or are you asking how to write a stored procedure?
– Garrison Becker
Dec 27 '18 at 20:44
I feel comfortable in this situation with writing the proc because I'm basically just calling for procedures in the body of this one with their own parameters. My biggest question is how to capture output of one of the procedures in the body to be used in the others
– Tom N.
Dec 27 '18 at 20:53
Pass the supplied parameters into each sproc, and output the result of the first sproc to a variable. Are you asking how to output the result of the first sproc to where you can use it in the other 3? Or are you asking how to write a stored procedure?
– Garrison Becker
Dec 27 '18 at 20:44
Pass the supplied parameters into each sproc, and output the result of the first sproc to a variable. Are you asking how to output the result of the first sproc to where you can use it in the other 3? Or are you asking how to write a stored procedure?
– Garrison Becker
Dec 27 '18 at 20:44
I feel comfortable in this situation with writing the proc because I'm basically just calling for procedures in the body of this one with their own parameters. My biggest question is how to capture output of one of the procedures in the body to be used in the others
– Tom N.
Dec 27 '18 at 20:53
I feel comfortable in this situation with writing the proc because I'm basically just calling for procedures in the body of this one with their own parameters. My biggest question is how to capture output of one of the procedures in the body to be used in the others
– Tom N.
Dec 27 '18 at 20:53
add a comment |
1 Answer
1
active
oldest
votes
Use the GET DIAGNOSTICS statement.
--#SET TERMINATOR @
SET SERVEROUTPUT ON@
CREATE OR REPLACE PROCEDURE TEST1(P_FIRST_PROC_ID INT)
BEGIN
RETURN P_FIRST_PROC_ID;
END@
BEGIN
DECLARE V_RC INT;
CALL TEST1(10);
GET DIAGNOSTICS V_RC = DB2_RETURN_STATUS;
CALL DBMS_OUTPUT.PUT_LINE('Return Status: '||V_RC);
END@
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
Yes. See the example above, which must print theReturn Status: 10string if you run it with DB2 CLP.
– Mark Barinstein
Dec 28 '18 at 8:16
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53950308%2fwrapping-procedure-calls-in-one-main-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use the GET DIAGNOSTICS statement.
--#SET TERMINATOR @
SET SERVEROUTPUT ON@
CREATE OR REPLACE PROCEDURE TEST1(P_FIRST_PROC_ID INT)
BEGIN
RETURN P_FIRST_PROC_ID;
END@
BEGIN
DECLARE V_RC INT;
CALL TEST1(10);
GET DIAGNOSTICS V_RC = DB2_RETURN_STATUS;
CALL DBMS_OUTPUT.PUT_LINE('Return Status: '||V_RC);
END@
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
Yes. See the example above, which must print theReturn Status: 10string if you run it with DB2 CLP.
– Mark Barinstein
Dec 28 '18 at 8:16
add a comment |
Use the GET DIAGNOSTICS statement.
--#SET TERMINATOR @
SET SERVEROUTPUT ON@
CREATE OR REPLACE PROCEDURE TEST1(P_FIRST_PROC_ID INT)
BEGIN
RETURN P_FIRST_PROC_ID;
END@
BEGIN
DECLARE V_RC INT;
CALL TEST1(10);
GET DIAGNOSTICS V_RC = DB2_RETURN_STATUS;
CALL DBMS_OUTPUT.PUT_LINE('Return Status: '||V_RC);
END@
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
Yes. See the example above, which must print theReturn Status: 10string if you run it with DB2 CLP.
– Mark Barinstein
Dec 28 '18 at 8:16
add a comment |
Use the GET DIAGNOSTICS statement.
--#SET TERMINATOR @
SET SERVEROUTPUT ON@
CREATE OR REPLACE PROCEDURE TEST1(P_FIRST_PROC_ID INT)
BEGIN
RETURN P_FIRST_PROC_ID;
END@
BEGIN
DECLARE V_RC INT;
CALL TEST1(10);
GET DIAGNOSTICS V_RC = DB2_RETURN_STATUS;
CALL DBMS_OUTPUT.PUT_LINE('Return Status: '||V_RC);
END@
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
Use the GET DIAGNOSTICS statement.
--#SET TERMINATOR @
SET SERVEROUTPUT ON@
CREATE OR REPLACE PROCEDURE TEST1(P_FIRST_PROC_ID INT)
BEGIN
RETURN P_FIRST_PROC_ID;
END@
BEGIN
DECLARE V_RC INT;
CALL TEST1(10);
GET DIAGNOSTICS V_RC = DB2_RETURN_STATUS;
CALL DBMS_OUTPUT.PUT_LINE('Return Status: '||V_RC);
END@
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
edited Dec 28 '18 at 8:15
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
answered Dec 27 '18 at 21:05
Mark Barinstein
1,07914
1,07914
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
Yes. See the example above, which must print theReturn Status: 10string if you run it with DB2 CLP.
– Mark Barinstein
Dec 28 '18 at 8:16
add a comment |
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
Yes. See the example above, which must print theReturn Status: 10string if you run it with DB2 CLP.
– Mark Barinstein
Dec 28 '18 at 8:16
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
So if my first call returns resource-I'd, I can use that statement to return that and use it in the next call?
– Tom N.
Dec 27 '18 at 21:15
Yes. See the example above, which must print the
Return Status: 10 string if you run it with DB2 CLP.– Mark Barinstein
Dec 28 '18 at 8:16
Yes. See the example above, which must print the
Return Status: 10 string if you run it with DB2 CLP.– Mark Barinstein
Dec 28 '18 at 8:16
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53950308%2fwrapping-procedure-calls-in-one-main-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Pass the supplied parameters into each sproc, and output the result of the first sproc to a variable. Are you asking how to output the result of the first sproc to where you can use it in the other 3? Or are you asking how to write a stored procedure?
– Garrison Becker
Dec 27 '18 at 20:44
I feel comfortable in this situation with writing the proc because I'm basically just calling for procedures in the body of this one with their own parameters. My biggest question is how to capture output of one of the procedures in the body to be used in the others
– Tom N.
Dec 27 '18 at 20:53