Have Geek Brain

Integer in MySQL subqueries in storage procedure

I made a simple procedure to demonstrate

 CREATE PROCEDURE `demo`(demo_int int)
BEGIN
 DECLARE minid INT;
 SELECT min(id) FROM (SELECT id FROM events LIMIT demo_int,9999999999999999) as hoo INTO minid;
END$$

The problem is demo_int, if I change it to

LIMIT 1,9999999999999999

it works but

LIMIT demo_int,9999999999999999

Not ... This gives an error

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 'demo_int,9999999999999999) as hoo INTO minid; END' at line 4 (errno: 1064)

Any hints?

+2


a source to share


1 answer


The problem (whether its a bug or a feature is moot) is that you cannot use this parameter with a LIMIT statement. The LIMIT operator accepts integer constants, the key point in this case is that they must be constants , not variables .



See here for a submitted bug report and here for a possible workaround that involves using prepared statements.

+2


a source






More articles:


All Articles