Limit the number of selected rows using a stored procedure parameter in MySQL

Question

I have a SelectProc procedure that contains a SELECT statement. I want to add a LimitRowsCount procedure parameter and use it as follows:

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

but this approach does not work.

SELECT itself contains nested subqueries, so I cannot create a view from it. Is there a way more propper and then dynamic SQL (prepared statements)?

Answer 1

CREATE PROCEDURE SelectProc (IN LimitRowsCount INT) 
BEGIN

SET @LimitRowsCount1=LimitRowsCount; 

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

EXECUTE STMT USING @LimitRowsCount1; 

END

Answer 2

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 Guide - 12.2.8. Syntax SELECT

So no - you cannot.