Link to home
Start Free TrialLog in
Avatar of DerekWatling
DerekWatlingFlag for South Africa

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DerekWatling

ASKER

A convoluted way of doing it, but it works great!