mysql cursor and stored procedure problems
by mfoley from LinuxQuestions.org on (#4QWJ0)
Sorry to be such a mysql idjit! I have the stored procedure script shown below. I've followed examples on the web, but getting an error. Unfortunately, mysql error messages are not very helpful.
Code:DROP PROCEDURE IF EXISTS blueDuesNotice_sp;
DELIMITER //
CREATE procedure blueDuesNotice_sp()
BEGIN
CREATE TEMPORARY TABLE memberDues (
personId mediumint,
duesType varchar(60),
amount decimal(6,2)
);
CREATE TEMPORARY TABLE duesLetter (
mailTo varchar(60),
addrLine1 varchar(60),
addrLine2 varchar(60),
addrLine3 varchar(60),
ZIPcode varchar(20),
email varchar(80),
duesType varchar(60),
duesAmount decimal(6,2)
);
-- This year's 50+ dues
insert into memberDues select personId, 'Endowment:', 28
from orgMember
where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears >= 50;
-- This year's regular dues
insert into memberDues select personId, 'Regular Dues:', 88
from orgMember
where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears < 50;
DECLARE xxmailTo varchar(60);
DECLARE xxaddrLine1 varchar(60);
DECLARE xxaddrLine2 varchar(60);
DECLARE xxaddrLine3 varchar(60);
DECLARE xxphone varchar(20);
DECLARE xxZIPcode varchar(20);
DECLARE xxemail varchar(80);
DECLARE xxduesType varchar(60);
DECLARE xxduesAmount decimal(6,2);
DECLARE MyCursor CURSOR FOR
select a.mailTo, a.addrLine1, a.addrLine2, coalesce(a.addrLine3,'') addrLine3,
coalesce(a.homePhone,'') phone, coalesce(a.email,'') email, d.duesType, d.amount
from memberDues d
join vwMailingAddress a on a.personId = d.personId
order by d.personId;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN myCursor;
getDues: LOOP
FETCH myCursor INTO xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxphone, xxZIPcode, xxemail, xxduesType, xxduesAmount
IF finished = 1
THEN
LEAVE getDues;
END IF;
insert into duesLetter select xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxZIPcode, xxemail, xxduesType, xxduesAmount;
END LOOP getDues;
CLOSE myCursor;
select * from duesLetter;
END //and the error is:
Code:ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE xxmailTo varchar(60);
DECLARE xxaddrLine1 varchar(60);
DECLARE xxaddrLi' at line 32The line numbers appear meaningless. Line 5 is the "CREATE procedure" statement. Line 32 is the second "insert into memberDues" statement. I've previously tested through and excluding the "DECLARE xxmailTo". Now I'm trying to declare some local variables and create a cursor. I've done this 4-zillion times on SQL Server, first time trying with mysql.
Code:DROP PROCEDURE IF EXISTS blueDuesNotice_sp;
DELIMITER //
CREATE procedure blueDuesNotice_sp()
BEGIN
CREATE TEMPORARY TABLE memberDues (
personId mediumint,
duesType varchar(60),
amount decimal(6,2)
);
CREATE TEMPORARY TABLE duesLetter (
mailTo varchar(60),
addrLine1 varchar(60),
addrLine2 varchar(60),
addrLine3 varchar(60),
ZIPcode varchar(20),
email varchar(80),
duesType varchar(60),
duesAmount decimal(6,2)
);
-- This year's 50+ dues
insert into memberDues select personId, 'Endowment:', 28
from orgMember
where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears >= 50;
-- This year's regular dues
insert into memberDues select personId, 'Regular Dues:', 88
from orgMember
where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears < 50;
DECLARE xxmailTo varchar(60);
DECLARE xxaddrLine1 varchar(60);
DECLARE xxaddrLine2 varchar(60);
DECLARE xxaddrLine3 varchar(60);
DECLARE xxphone varchar(20);
DECLARE xxZIPcode varchar(20);
DECLARE xxemail varchar(80);
DECLARE xxduesType varchar(60);
DECLARE xxduesAmount decimal(6,2);
DECLARE MyCursor CURSOR FOR
select a.mailTo, a.addrLine1, a.addrLine2, coalesce(a.addrLine3,'') addrLine3,
coalesce(a.homePhone,'') phone, coalesce(a.email,'') email, d.duesType, d.amount
from memberDues d
join vwMailingAddress a on a.personId = d.personId
order by d.personId;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN myCursor;
getDues: LOOP
FETCH myCursor INTO xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxphone, xxZIPcode, xxemail, xxduesType, xxduesAmount
IF finished = 1
THEN
LEAVE getDues;
END IF;
insert into duesLetter select xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxZIPcode, xxemail, xxduesType, xxduesAmount;
END LOOP getDues;
CLOSE myCursor;
select * from duesLetter;
END //and the error is:
Code:ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE xxmailTo varchar(60);
DECLARE xxaddrLine1 varchar(60);
DECLARE xxaddrLi' at line 32The line numbers appear meaningless. Line 5 is the "CREATE procedure" statement. Line 32 is the second "insert into memberDues" statement. I've previously tested through and excluding the "DECLARE xxmailTo". Now I'm trying to declare some local variables and create a cursor. I've done this 4-zillion times on SQL Server, first time trying with mysql.