DerekWatling
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER