--- In firebird-support@yahoogroups.com, "Stephen Davey" <stephen@...>
wrote:

>
> I use this structure to table maintenance processes.
>
> The simple question is , can it be done in a better way ?
>

Much. Firstly you are using a count instead of an exists check. That
is *** really *** expensive. In this case, it is probably using a
primary key to narrow it down so it is less expensive, but get in the
habit instead of using exists:

eg.

IF (NOT EXISTS(SELECT 1 FROM BALE WHERE BALE.CODE = :CODE)) THEN
BEGIN
...
END

The exists check returns the answer as soon as it finds a single
record, whereas the count will need to find EVERY record that matches
your criteria before returning, and you don't even care about the number.

But in this case, I would use a shortcut for this problem.

Firebird 1.5 or higher, the ROW_COUNT can be used within a stored
procedure to return the number of records that were affected by an update.

So I would rewrite your procedure like this:

> SET TERM ^ ;
> CREATE PROCEDURE BALE_SAVE
> (
> CODE VARCHAR(30),
> FABRICCODE VARCHAR(20),
> FABRICDESCR VARCHAR(60),
> APMCODE VARCHAR(20),
> APMDESCR VARCHAR(60),
> QTYEST NUMERIC(15,2)
> )
> AS
> DECLARE VARIABLE CNT INTEGER ;
> BEGIN
>
> UPDATE BALE
> SET
> FABRICCODE = :FABRICCODE,
> FABRICDESCR = :FABRICDESCR,
> APMCODE = :APMCODE,
> APMDESCR = :APMDESCR,
> QTYEST = :QTYEST
> WHERE
> CODE = :CODE ;

IF ((ROW_COUNT) = 0) THEN
BEGIN
INSERT INTO BALE(CODE, FABRICCODE, .....) VALUES (:CODE, :FABRICCODE,
....) ;
END

> END ^

Adam