Robert,

On Wed, 3 Oct 2001 02:25:52 +0000 (UTC), tultalk@...
("Robert F. Tulloch") wrote:

>Hi:
>
> Well, I get it to Prepare in IBConsole: Question is will something
>like this work?

At least you should put BEGIN ... END around your three updates.

What do you want the final value to be, if param2 = param3 or param2 =
param5 or param4 = param5? In your proc some records would be updated
more than once.

>
>SET TERM ## ;
>CREATE PROCEDURE UPDATEZORRO (DATE1 DATE, PARAM1 INTEGER, YEAR1
>INTEGER,
>PARAM2 INTEGER, PARAM3 INTEGER, PARAM4 INTEGER, PARAM5 INTEGER, PARAM6
>INTEGER)
>AS
> DECLARE VARIABLE RdID INTEGER;
>BEGIN
> FOR SELECT ID
> FROM MEMBERS
> WHERE STATUS_MEM = 'ACTIVE'
> INTO RdID
> DO
> UPDATE MEMPAY2
> SET RENEW_DUES = :PARAM1
> WHERE (ID = :RdID AND RENEW_DATE >= :DATE1 AND
> YEARM2 >= :YEAR1 AND
> RENEW_DUES = :PARAM2);
>
> UPDATE MEMPAY2
> SET RENEW_DUES = :PARAM3
> WHERE (ID = :RdID AND RENEW_DATE >= :DATE1 AND
> YEARM2 >= :YEAR1 AND
> RENEW_DUES = :PARAM4);
>
> UPDATE MEMPAY2
> SET RENEW_DUES = :PARAM5
> WHERE (ID = :RdID AND RENEW_DATE >= :DATE1 AND
> YEARM2 >= :YEAR1 AND
> RENEW_DUES = :PARAM6);
>
>
>END ##
>SET TERM ; ##

Something like this updates each row at most once:

CREATE PROCEDURE ...
AS
DECLARE VARIABLE vid INTEGER;
DECLARE VARIABLE vrd INTEGER;
BEGIN
FOR SELECT p.id, p.renew_dues
FROM members m JOIN mempay2 p ON (m.id = p.id)
WHERE m.status_mem = 'ACTIVE'
AND p.renew_date >= :date1
AND p.yearm2 >= :year1
INTO :vid, :vrd
DO BEGIN
IF (:vrd = :param1) THEN BEGIN
UPDATE mempay2 SET renew_dues = :param2 WHERE id = :vid;
END
ELSE IF (:vrd = :param3) THEN BEGIN
UPDATE mempay2 SET renew_dues = :param4 WHERE id = :vid;
END
ELSE IF (:vrd = :param5) THEN BEGIN
UPDATE mempay2 SET renew_dues = :param6 WHERE id = :vid;
END
END
END

Maybe you have to select more fields from mempay2 and use them in the
WHERE clauses of the UPDATE statements, if id does not uniquely
identify rows in mempay2.

.02 by
Carl van Tast