Re: CRUD stored procedure question

On Mon, 22 Nov 2021 at 17:44, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

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.

I tend to like this approach as well. When the caller passes in NULL,
I ignore the column. Even here, there are pitfalls. Say the user did
an update and accidentally set DECEASE_DATE. They immediately
recognise the error, and want to set the date back to NULL. If the
code interprets NULL to mean 'ignore', then you I can't really set it
back to NULL.

One solution is to discard the idea of updating all the fields in a
record all in one go. Now, instead of updateCustomerRecord(), I have
setDeceaseDate. Accept whatever gets passed in, including NULL, and
that's that. Yes, yes, yes, performance. Have you tried it yourself
both ways, once updating the three columns you need updated, and once
updating every column despite only needing to update the address? It's
quite an interesting exercise to look at journal entries and analyse
what the most common transactions actually are.

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.

This is my idea of utopia!

The two things I try very, very hard to avoid:
1) Pass an entire record image/structure from one routine to another,
even internally
2) Pass internal database codes (CREDRATE='J') between the database
and the outside, like a stored procedure populating a 'record'

--buck