1

I want to use variable in MySQL limit, but it is not valid. Why? (If I change variable to number, it's ok.)

MySQL 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 '@records-leaveErr; END IF; END IF; END' at line 9

My code:

DELIMITER //
    CREATE PROCEDURE `deleteOldErrRecords`(backTime int(11), leaveErr int(11))
    BEGIN
      SELECT @records:=COUNT(*) FROM `sys_proc_err`;
      IF @records > leaveErr THEN
        SELECT @actRecords:=COUNT(*) FROM `sys_proc_err` WHERE `time` > (NOW()-backTime); 
        IF @actRecords > leaveErr THEN
          DELETE FROM `sys_proc_err` WHERE `time` < (NOW()-backTime);
        ELSE
          DELETE FROM `sys_proc_err` ORDER BY `id` ASC LIMIT @records-leaveErr;
        END IF;
      END IF;
    END //
DELIMITER ;
dev0experiment
  • 462
  • 1
  • 7
  • 22
  • If you don't have MySQL 5.5.6+, you can find many workarounds in http://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql. – Gruber Jan 09 '13 at 12:54

1 Answers1

5

Using variables for limit values is only supported since 5.5.6+.

Marc B
  • 356,200
  • 43
  • 426
  • 500