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.

Bryan Menard · Accepted Answer · 2009-12-03 03:36:03Z

4

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;

answered Dec 3, 2009 at 3:36

Bryan Menard

13.4k4 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pilcrow Over a year ago

+1 for the diagnosis and workaround. MySQL bug entry #11918 discusses just this peculiarity.

Vincent Ramdhanie Over a year ago

There is some discussion here: dev.mysql.com/doc/refman/5.0/en/select.html in one of the comments about half way down the page marked <strong>Posted by Marc Grue on June 24 2006 10:44am</strong>. I don't know how to link directly to the comment.

user43040 Over a year ago

Thank you very much for the concise answer and work-around. Works a treat.

alain.janinm · Accepted Answer · 2012-04-28 18:42:30Z

1

The direct link to the Marc Grue's comments.

Trick to do this : Get the table id of the comment and create an anchor in the URL.

edited Apr 28, 2012 at 18:42

alain.janinm

20.1k11 gold badges67 silver badges114 bronze badges

answered Jul 4, 2011 at 18:46

Cristiano Gois

111 bronze badge

Collectives™ on Stack Overflow

Frustrating MySQL CREATE PROCEDURE error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related