Article 4QWJ0 mysql cursor and stored procedure problems

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.latest?d=yIl2AUoC8zA latest?i=zi6g7zdqd10:iU-X9Nue0cI:F7zBnMy latest?i=zi6g7zdqd10:iU-X9Nue0cI:V_sGLiP latest?d=qj6IDK7rITs latest?i=zi6g7zdqd10:iU-X9Nue0cI:gIN9vFwzi6g7zdqd10
External Content
Source RSS or Atom Feed
Feed Location https://feeds.feedburner.com/linuxquestions/latest
Feed Title LinuxQuestions.org
Feed Link https://www.linuxquestions.org/questions/
Reply 0 comments