Hi:

Same old crap I keep forgetting about the : at *** below caused
Invalid Command error
on prepare. I will never understand this as examples show things like

if(:mngr_no IS NULL) THEN

Ran into this a while back and I think it was Helen who disputed
it.

DataDef:

Note Variable names do not need to be—and must not be—preceded by a
colon in
stored procedures except in SELECT, INSERT, UPDATE, and DELETE clauses
where they would
be interpreted as column names without the colon. Page 150 bottom.


SET TERM ## ;
CREATE PROCEDURE MEMBERDUESCHANGE (EFFECTIVEDATE DATE, EFFECTIVEYEAR
INTEGER,
OLDPAY1 INTEGER, NEWPAY1 INTEGER,
OLDPAY2 INTEGER, NEWPAY2 INTEGER,
OLDPAY3 INTEGER, NEWPAY3 INTEGER)
AS
DECLARE VARIABLE vid INTEGER;
DECLARE VARIABLE vrate INTEGER;
BEGIN
FOR SELECT m.id, p.renew_dues
FROM members m JOIN mempay2 p ON (m.id = p.id)
WHERE (m.status_mem = 'ACTIVE' OR m.status_mem = 'CANCELLED')
AND p.renew_date >= :effectivedate
AND p.yearM2 >= :effectiveyear
INTO vid, vrate
DO BEGIN
IF (vrate = oldpay1) THEN <---Changed from (:vrate =
:oldpay1) ***
BEGIN
UPDATE mempay2 SET renew_dues = :newpay1
WHERE id = :vid AND yearm2 >= :effectiveyear;
END
ELSE IF (vrate = oldpay2) THEN
BEGIN
UPDATE mempay2 SET renew_dues = :newpay2
WHERE id = :vid AND yearm2 >= :effectiveyear;
END
ELSE IF (vrate = OLDPAY3) THEN
BEGIN
UPDATE mempay2 SET renew_dues = :newpay3
WHERE id = :vid AND yearm2 >= :effectiveyear;
END
END

END ##
SET TERM ; ##