Search code examples
mysqlsqlprocedures

Create mysql procedure

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
Solution

  • 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