If you do it with NULL parameters as has been mentioned, in the update just COALESCE the fields in the SET to their current value if the new value is null. You might first want to check they're not all null so as to avoid the update entirely when nothing has changed.
update WORKSHEET
set (
WORK_DATE,
WORK_TIME
) = (
coalesce(NEW_DATE_VALUE, WORK_DATE),
coalesce(NEW_TIME_VALUE, WORK_TIME)
)
Birgitta's extended indicator suggestion looks interesting too!
I also agree with what's been said regarding CRUD procedures, just emulating create, read, write... etc. doesn't achieve much, it reminds me of the old idea of people doing that in RPG, which I never understood at all.
Tim.
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of K Crawford <kscx3ksc@xxxxxxxxx>
Sent: 22 November 2021 22:18
To: RPG400-L@xxxxxxxxxxxxxxxxxx <RPG400-L@xxxxxxxxxxxxxxxxxx>
Subject: CRUD stored procedure question
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
midrange.com
