Create mysql procedure

Question

I'm trying to create procedure in mysql database this procedure will move accounts from table to another this is my procedure but I have error with syntax

CREATE PROCEDURE radius.archive_acct()
BEGIN
 INSERT INTO radacctold
 SELECT * FROM radacct
 WHERE acctstoptime > 0
 AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
 DELETE FROM radacct
WHERE acctstoptime > 0
AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
END

this is the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

Answer 1

You should change the normal delimiter to some other value and then create and end the procedure and reset the delimiter.

DELIMITER //
CREATE PROCEDURE radius.archive_acct()
BEGIN
 INSERT INTO radacctold
 SELECT * FROM radacct
 WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
 DELETE FROM radacct
 WHERE acctstoptime > 0
 AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
END //
DELIMITER ;

For more insights see: Getting Started with MySQL Stored Procedures

Answer 2

Stored procedures structures are made for supporting multiple executable statements.
And each statements needs a terminator, which is by default semicolon. ;.

It is required to instruct mysql engine to stop execute immediately every terminated statement, until the body structure is processed compiled.

We need a DELIMITER statement to suppress the default terminator temporarily.
Your code did not use such DELIMITER.

Following example will help fixing your code.

-- define the delimiter
delimiter //


--- place here your stored procedure

-- and lastly following line
//

-- reset the delimiter
delimiter ;

Read my answer to a similar question here: https://stackoverflow.com/a/23160180/767881