Using LIMIT as a variable prior to MySQL Version 5.5.6+

Question

anyone knows how could I write the following query on an older version of MySQL (older than 5.5.6+), thanks.

CREATE PROCEDURE `getweekact`(userid int(10), topVal int(4))
BEGIN
  SELECT uniqueid, firstname, lastname, clock FROM users, act
  WHERE users.uniqueid = act.act_uniqueid
  AND clock >= DATE_SUB(CURDATE(), INTERVAL 8 DAY)
  GROUP BY uniqueid
  ORDER BY clock DESC
  LIMIT topVal;
END;

possible duplicate of [Variable LIMIT Clause in MySQL](http://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql) (but +1 for spotting that "[Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.](http://dev.mysql.com/doc/refman/5.5/en/select.html)") — RandomSeed, Jul 28 '14 at 09:55
This doesn't answer my question, I know about version 5.5.6 and I already read http://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql prior to posting this post. — carol1287, Jul 28 '14 at 10:04
That was meant as a compliment... However, [this answer](http://stackoverflow.com/a/10025538/1446005) does address your question. — RandomSeed, Jul 28 '14 at 10:05

score 0 · Answer 1 · edited May 23 '17 at 12:20

Here is the final query based on https://stackoverflow.com/a/10025538/1446005 post. I overlooked the final answer on that post. Thanks @RandomSeed for pointing that out.

CREATE PROCEDURE `getweekact`(userid int(10), topVal int(4))
BEGIN
  SET @_topVal = topVal;
  PREPARE stmt FROM "SELECT uniqueid, firstname, lastname, clock FROM users, act WHERE users.uniqueid = act.act_uniqueid AND clock >= DATE_SUB(CURDATE(), INTERVAL 8 DAY) GROUP BY uniqueid ORDER BY clock DESC LIMIT ?;";
  EXECUTE stmt USING @_topVal;
  DEALLOCATE PREPARE stmt;
END

Using LIMIT as a variable prior to MySQL Version 5.5.6+

1 Answers1