Limiting selected rows count with a stored procedure parameter in MySQL

Question

I have a procedure SelectProc wich contains 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 propper then dynamic SQL (prepared statements) ?

Answer 1

From the manual:

The LIMIT clause can be used to constrain the number of rows 
returned by the SELECT statement. LIMIT takes one or two numeric 
arguments, which must both be nonnegative integer constants  
(except when using prepared statements). 

MySQL Manual - 12.2.8. SELECT Syntax

So that's a no - you cannot.

Answer 2

CREATE PROCEDURE SelectProc (IN LimitRowsCount INT) 
BEGIN

SET @LimitRowsCount1=LimitRowsCount; 

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

EXECUTE STMT USING @LimitRowsCount1; 

END