Subject Re: Calling an UPDATE procedure
Author Adam
--- In, "markus_6048"
<markus.brauchli@...> wrote:
> I want to start working with procedures. I wrote a first one for
> updating the address table. I included all fields of the table. The
> problem I have now is, that not all of the fields change all the time.
> But as far as I understand the documentations, all input parameter
> have to be passed over at the moment the application calls the
> procedure. How do I have to call my procedure if only just one field
> changed -> has to updated? Let's assume that we have to manage a
> moovement: STREET, ZIP, TOWN change. All other fields remain
> unchanged. How do I pass over just these three changed fields?

You would have to pass them in as a flag (eg NULL) and handle such
situations inside the procedure. In my experience, it is probably more
work for the database server to test and update the fields separately
than to just include the entire record.

Alternatively you may create a procedure that handles just those
fields, and call that second procedure from inside this procedure.
Your application could either directly call the second procedure in
such cases, or you could put logic into the ADR_UPDATE procedure to
only call these 'worker' procedures as required.

This suggestion is better suited to a more normalised structure rather
than a flat table, where not updating certain fields means you don't
need to run an update on a given table at all.

For a few hundred bytes on a single table I wouldn't bother, it will
just make the code harder to maintain and unless you are calling the
procedure frequently enough to make the millisecond performance
improvement useful, spend time optimising some other part of your
software for a better return on investment.