Search code examples
mysqlselectlimitprocedure

Limiting selected rows count with a stored procedure parameter in MySQL

I have a procedure SelectProc which contains a SELECT statement. I want to add a procedure param LimitRowsCount and use it as following:

CREATE PROCEDURE SelectProc (IN LimitRowsCount INTEGER UNSIGNED) 
BEGIN
   SELECT (...)
   LIMIT LimitRowsCount;
END

but this approach doesn't work.

The SELECT itself contains nested subqueries so I can't create view from it. Is there a way more proper than dynamic SQL (prepared statements)?

Solution 
  • CREATE PROCEDURE SelectProc (IN LimitRowsCount INT) 
BEGIN

SET @LimitRowsCount1=LimitRowsCount; 

PREPARE STMT FROM "SELECT (...) LIMIT ?";

EXECUTE STMT USING @LimitRowsCount1; 

END