|
Embedded SQL or SQL PL doesn't matter.
You have to define an Indicator Variable for each Column and initialize it
with -7.
... and then change the Indicator-Variable to 0 if a value is passed.
In the update-Statement you specify the indicator variable after the
Parameter-Value.
Something like this:
Declare Ind1 SmallInt Default -7;
Declare Ind2 SmallInt Default -7;
....
Declare IndN SmallInt Default 7;
Set Ind1 = Case When Param1 is Not NULL Then 0 Else -7 End,
Ind2 = Case When Param2 is Not NULL Then 0 Else -7 End;
...
IndN = Case When ParamN is Not NULL Then 0 Else -7 End;
Update YourTable
Set YourCol1 = Param1 Ind1,
YourCol2 = Param2 Ind2,
...
YourColN = ParamN Ind2;
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 Tim
Fathers
Sent: Dienstag, 23. November 2021 10:54
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: CRUD stored procedure question
Birgitta, I'm assuming this is in embedded SQL right? Is there a way to do
this in a pure SQL stored procedure?
Tim.
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Birgitta
Hauser <Hauser@xxxxxxxxxxxxxxx>
Sent: 23 November 2021 06:05
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: 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
--
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
--
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
