Limiting selected rows count with a stored procedure parameter in MySQL

limit, mysql, procedure, select

Solution

CREATE PROCEDURE SelectProc (IN LimitRowsCount INT) 
BEGIN

SET @LimitRowsCount1=LimitRowsCount; 

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

EXECUTE STMT USING @LimitRowsCount1; 

END

Problem

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)?

Original source