[SOLVED] Need help with mysql stored procedure syntax
by mfoley from LinuxQuestions.org on (#4QVGS)
I have the following mysql script:
Code:DELIMITER //
ALTER procedure blueDuesNotice_sp()
BEGIN
CREATE TEMPORARY TABLE memberDues (
personId mediumint,
amount decimal(6,2);
);
insert into memberDues select personId, 28
from orgMembers
where orgId = 10 and standing = 1 and serviceYears >= 50;
select * from memberDues;
END //When I try to execute this, I get the error:
Code:ERROR 1064 (42000) at line 2: 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 '()
BEGIN
CREATE TEMPORARY TABLE memberDues (
personId mediumint,
amount' at line 1Privileges are:
Code:> show grants for 'user'@'localhost'
GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'localhost'I can't see the problem. If I replace everything between the BEGIN/END with a select statement, it works fine. Is there a problem with my CREATE TEMPORARY TABLE syntax? I've checked Internet references on this and don't see anything wrong.
Code:DELIMITER //
ALTER procedure blueDuesNotice_sp()
BEGIN
CREATE TEMPORARY TABLE memberDues (
personId mediumint,
amount decimal(6,2);
);
insert into memberDues select personId, 28
from orgMembers
where orgId = 10 and standing = 1 and serviceYears >= 50;
select * from memberDues;
END //When I try to execute this, I get the error:
Code:ERROR 1064 (42000) at line 2: 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 '()
BEGIN
CREATE TEMPORARY TABLE memberDues (
personId mediumint,
amount' at line 1Privileges are:
Code:> show grants for 'user'@'localhost'
GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'localhost'I can't see the problem. If I replace everything between the BEGIN/END with a select statement, it works fine. Is there a problem with my CREATE TEMPORARY TABLE syntax? I've checked Internet references on this and don't see anything wrong.