mysqlmysql-error-1064mysql-5.1

Procedure Parameter issue - Mysql 5.1 SQL Error 1064

The following works in MYSQL 5.5 windows, doesn't work in MYSQL 5.1, ubuntu lucid

DELIMITER $$

CREATE PROCEDURE `queueup`( IN groupsize INT, OUT done INT)
BEGIN

    DECLARE room INT;
    SET room = -1;
    UPDATE queue SET status = 'PLACED', assigned_room = room WHERE status = 'WAITING' ORDER BY queue_position ASC LIMIT groupsize;
END $$

SQL 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 'groupsize;

Solution

  • In MySQL 5.5 LIMIT clause can be used with variables; in your case 'groupsize' is a variable.

    From the reference - Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.