Frustrating MySQL CREATE PROCEDURE error

Question

Hopefully this is a nice quick one to resolve.

Here is my .sql file:

USE my_db;

DELIMITER $$
CREATE PROCEDURE searchLocation(IN argQuery VARCHAR(32), IN argLimit INT)
BEGIN
  SELECT DISTINCT `suburb`, `postcode`
  FROM `location`
  WHERE `suburb` LIKE '%argQuery%'
  OR `postcode` LIKE 'argQuery%'
  LIMIT argLimit
  ;
END
$$
DELIMITER ;

This is the output:

ERROR 1064 (42000) at line 4: 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 'argLimit
  ;
END' at line 8

So it appears to not like my parameter argLimit, but I can't work out why. I must be doing something silly.

I'm using MySQL 5.0.51.

Thanks.

Answer 1

LIMIT must be a constant and cannot be parametrized inside a procedure or function in MySQL. However, it's possible to workaround this issue by using the PREPARE ... EXECUTE ... USING syntax.

It might end up looking somewhat like this:

...
SET @qry= argQuery;
SET @lmt= argLimit;

PREPARE stmt FROM 'SELECT ... LIKE ? ... LIKE ? ... LIMIT ?';
EXECUTE stmt USING @qry, @qry, @lmt;
DEALLOCATE PREPARE stmt;