Limit gives error in a Stored routine

Hi group,

I have this following stored routine which is not compiling.

CREATE procedure Test3(IN a INT(2), IN b INT(2))
BEGIN
SELECT * FROM USERS LIMIT a, b;
END

I get the following error:

ERROR 1064 (42000): 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 'a, b;

jackal_on_work@yahoo.com wrote:

> I have this following stored routine which is not compiling.
>
> CREATE procedure Test3(IN a INT(2), IN b INT(2))
> BEGIN
> SELECT * FROM USERS LIMIT a, b;
> END

This doesn't work. The parameters to LIMIT must be literals, not
variables. However there is a trick to make it work - convert the
statement to a prepared statement:

mysql> CREATE PROCEDURE p1(IN a INT, IN b INT)
-> BEGIN
-> SET @stmt=CONCAT('SELECT * FROM t1 LIMIT ', a, ',', b);
-> PREPARE s1 FROM @stmt;
-> EXECUTE s1;
-> END

mysql> select * from t1;
+------+
| c1 |
+------+
| 69 |
| 90 |
| 41 |
| 36 |
+------+

mysql> call p1(0,3);
+------+
| c1 |
+------+
| 69 |
| 90 |
| 41 |
+------+

mysql> call p1(1,2);
+------+
| c1 |
+------+
| 90 |
| 41 |
+------+


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/