Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

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
0
DerekWatling
Asked:
DerekWatling
1 Solution
 
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now