On 26 May 2004 at 17:21, Dixon Epperson wrote:

>still a little unsure of my procedure. I wrote this, (first time
>I've used a while do statement) and it seems to work fine. but
>would appreciate an expert opinion.

I don't like your approach of multiple updating the same record.
Besides, the purpose of your procedure is not clear. What are you
trying to do?

>SET TERM !!
> DROP PROCEDURE GETINVOICENO!!
> COMMIT!!
>
> CREATE PROCEDURE GETINVOICENO
> RETURNS (hightn INTEGER) AS
> DECLARE VARIABLE pinv VARCHAR(20);
> BEGIN
> UPDATE LUTABLE SET LU_RATE=LU_RATE + 1 WHERE LU_FIELD='INVNO';

May be you can use a variable instead of database field?

> BEGIN
> SELECT LU_RATE FROM LUTABLE WHERE LU_FIELD='INVNO' INTO
>hightn ;
> SELECT SA_INVOICE FROM SALESREG WHERE SA_INVOICE=:hightn
> INTO pinv;
> WHILE (pinv = hightn) DO
> BEGIN
> UPDATE LUTABLE SET LU_RATE=LU_RATE + 1 WHERE
>LU_FIELD='INVNO';
> SELECT LU_RATE FROM LUTABLE WHERE LU_FIELD='INVNO'
> INTO hightn;
> SELECT SA_INVOICE FROM SALESREG WHERE SA_INVOICE=:hightn
> INTO pinv;
> END
> END
> SUSPEND;

Looks like your procedure search the first missed SA_INVOICE in
SALESREG... There is simplier way to achieve this.

Why not use simple SELECT FIRST 1 SA_INVOICE+1 FROM SALESREG S1
WHERE NOT EXISTS (SELECT * FROM SALESREG S2 WHERE
S2.SA_INVOICE=S1.SA_INVOICE+1)? Or the same with joins: SELECT FIRST
1 SA_INVOICE+1 FROM SALESREG S1 LEFT JOIN SALESREG S2 ON
S1.SA_INVOICE+1 = S2.SA_INVOICE WHERE S2.SA_INVOICE IS NULL

If you indeed have to keep the result in LU_RATE table, you can do
_one_ update after the result has been found.

SY, Dimitry Sibiryakov.