使用MySQL中的存储过程参数限制选定的行数

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

我有一个过程SelectProc wich包含了SELECT语句。我想添加一个过程param LimitRowsCount并使用它如下:

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

SELECT本身包含嵌套的子查询，因此我无法从它创建视图。有比动态SQL(准备语句)更有效的方法吗?

2 个解决方案

#1

CREATE PROCEDURE SelectProc (IN LimitRowsCount INT) 
BEGIN

SET @LimitRowsCount1=LimitRowsCount; 

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

EXECUTE STMT USING @LimitRowsCount1; 

END

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

MySQL手册——12.2.8。选择语法

So that's a no - you cannot.

所以这是不，你不能。

#1

CREATE PROCEDURE SelectProc (IN LimitRowsCount INT) 
BEGIN

SET @LimitRowsCount1=LimitRowsCount; 

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

EXECUTE STMT USING @LimitRowsCount1; 

END

#2