Re: Has anyone monitored for a message within a stored procedure? -- MIDRANGE-L

This looks promising:
https://www.itjungle.com/2017/06/12/guru-error-handling-sql-pl-part-1/

Or this if IBMi supports resignal
https://www.ibm.com/docs/en/iis/11.5?topic=db2-handling-sql-errors

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx

------------------------------

message: 2
date: Tue, 7 Dec 2021 23:34:22 -0500
from: "Sue Romano" <slromano@xxxxxxxxxx>
subject: Re: Has anyone monitored for a message within a stored
procedure?

Another option is to use the IF EXISTS option with the DROP statement. The statement will complete with success whether the object exists or not.

DROP PROCEDURE IF EXISTS myproc;

This was introduced about a year ago.
https://www.ibm.com/support/pages/node/6340671

My first line is to Drop the Stored procedure before creating it. I
don't

want it to crash because the Stored Procedure does not exist.

Thank You

Tim Feldmeier

Sue Romano
Db2 for IBM i Development

------------------------------

message: 3
date: Wed, 8 Dec 2021 12:13:58 +0000
from: Rob Berendt <rob@xxxxxxxxx>
subject: RE: Has anyone monitored for a message within a stored
procedure?

Interesting. Introduced a year ago and that link you shared agrees with that, and says it's supported on 7.1.
Never thought they'd carry an enhancement back to 7.1 this late in the game.
Hmm, I don't see it documented here:
https://www.ibm.com/docs/en/i/7.1?topic=statements-drop
nor here
https://www.ibm.com/docs/en/i/7.2?topic=statements-drop
I don't see it until
https://www.ibm.com/docs/en/i/7.3?topic=statements-drop
Did they just not update the Knowledge Center for the older versions? Or was the link you provided in error?

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Sue Romano
Sent: Tuesday, December 7, 2021 11:34 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Has anyone monitored for a message within a stored procedure?

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Another option is to use the IF EXISTS option with the DROP statement. The
statement will complete with success whether the object exists or not.

DROP PROCEDURE IF EXISTS myproc;

This was introduced about a year ago.
https://www.ibm.com/support/pages/node/6340671

My first line is to Drop the Stored procedure before creating it. I don't

want it to crash because the Stored Procedure does not exist.

Thank You

Tim Feldmeier

Sue Romano
Db2 for IBM i Development
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

------------------------------

message: 4
date: Wed, 8 Dec 2021 13:55:45 +0000
from: "Belcher, Doug [COMRES/WR/STL]" <Doug.Belcher@xxxxxxxxxxx>
subject: RE: [EXTERNAL] Re: Has anyone monitored for a message within
a stored procedure?

Sue,

Tried this on a V7.3 system in ACS (drop table if exists ddblib/sensidta;), also STRSQL, and I'm getting an error "Keyword EXISTS not expected."

Doug Belcher | (he|him|his)
IT Applications Specialist | Comfort Control
Emerson Commercial & Residential Solutions | 8100 West Florissant Avenue | St. Louis, Mo | 63136 | USA
Office 314.553.5872 | Mobile 314.766.4461
Doug.Belcher@xxxxxxxxxxx
Please visit our Systems Support page to report an issue or request support.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Sue Romano
Sent: Tuesday, December 7, 2021 10:34 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: Has anyone monitored for a message within a stored procedure?

Another option is to use the IF EXISTS option with the DROP statement. The statement will complete with success whether the object exists or not.

DROP PROCEDURE IF EXISTS myproc;

This was introduced about a year ago.
https://urldefense.com/v3/__https://www.ibm.com/support/pages/node/6340671__;!!OkyjEqkqqe9UhNQ!i4q7YRtOTWggOufU0NXLy5mxrCqwIl9uQNJDbFA5olxGlTwDr3ENQMIzbp-FkKQFdIo$

My first line is to Drop the Stored procedure before creating it. I
don't

want it to crash because the Stored Procedure does not exist.

Thank You

Tim Feldmeier