RE: CRUD stored procedure question

You may define your stored procedures with Default values NULL. When ever a
NULL value is passed the appropriate column (value) is not passed.
Instead of using dynamic SQL, you may also use extended indicators.
An indicator variable can be set to -1 if an NULL value has to be updated,
but If you pass -5 the default value is used instead of a NULL value and if
you pass -7 the column value is just ignored.
So my set all indicator variables to -7 first and only for the values passed
switch the indicator variable to 0.
In this way you are even able to work with static SQL for the update.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles
Wilt
Sent: Montag, 22. November 2021 23:44
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: CRUD stored procedure question

Seems like an ideal place to use parms with DEFAULT NULL for all non-key
parms..

You'll need to use
PARAMETER STYLE SQL
or PARAMETER STYLE GENERAL WITH NULLS

Personally, I build all my stored procs with PARAMETER STYLE SQL.

Lastly, I hope that your "CRUD" procedure does more than just
Create/update/Delete a row in a particular table.
IMHO, "CRUD" procedures are pretty much worthless.

Rather than CRUD_INVHDR() and CRUD_INVDTL(), I have business routines
INVOICE_Create()
INVOCIE_AddLineItem()

AddlineItem() would for instance, add a record to INVDTL, but also perhaps
update the INVENTORY table or perhaps update a invoice total column in
INVHDR.

HTH,

Charles

On Mon, Nov 22, 2021 at 3:18 PM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

I am wondering how other people do CRUD stored procedures.
We have some CRUD stored procedures and I am wondering how you guys
handle the Update part. We are using RPG. Creating a procedure with
the input parms of an action code 'CRT', 'UPD', 'DLT' and all of the
columns to the table. The CRT and DLT are easy. But the UPD. The
caller may not know all of the values of all the columns. They know
the keys and the values they want updated. How do you handle the
values that are not to be changed?
We came up with if the column is char if the value is a pipe change
to blanks if blanks no change. other values then change it. Same type
of thing with numeric values -99999999 = zero, zero no change other
values then change.
For a small table this is okay but you get a table with 25+ columns,
what a programming pain on both sides.

Is there a better way?

--
KCrawford
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-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

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-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