Re: CRUD stored procedure question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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