× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

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

You'll need to use

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

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

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'


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2023 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.