Parameter to LIMIT number of records returned in stored procedure

I have a MySQL 5 stored procedure and want to pass in a parameter to specify how many records to return. If I use a hardcoded value the query works fine, but I get an error when trying to use the parameter.

CREATE PROCEDURE `RecentQueries`(IN _Limit INTEGER(11))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

  SELECT
    CONCAT(a.FirstName, ' ', a.Surname) AS Name,
    CONCAT(s.Company, IF(s.Branch = '', '', CONCAT(' (', s.Branch, ')'))) AS Office,
    ql.strQuery,
    ql.strQueryType,
    ql.strProperty,
    ql.tmeStamp,
    ql.lstCount
  FROM realinfo.tblquerylog ql
    JOIN cmainfo.tbl_agent a ON a.CustomerID = ql.refCustomerID
    JOIN cmainfo.tbl_subscriber s ON a.SubscriberID = s.SubscriberID
  ORDER BY ql.QueryLogID DESC
  LIMIT 20;
END;

Using "LIMIT 20" at the end works
Using "LIMIT _Limit" gives an error
DerekWatlingAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I fear that is a limitation of MySQL...
you might consider using prepared statements:

http://forums.mysql.com/read.php?98,69465,78682#msg-78682
0
 
DerekWatlingAuthor Commented:
A convoluted way of doing it, but it works great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.