MySQL 5.x: passing limits as stored procedure / function parameters

 

MySQL 5.x (at least 5.0.15 and earlier versions) does not allow using variables or procedure formal parameters with LIMIT. Here is a workaround:

CREATE PROCEDURE sp (
  IN LimitStart_ INT,
  IN LimitCnt_ INT
)
BEGIN
  SET @lim = CONCAT(‘ LIMIT ‘, LimitStart_, ‘,’, LimitCnt_);
  SET @q = "SELECT mycol FROM mytable";

  SET @q = CONCAT(@q, @lim);
  PREPARE st FROM @q;
  EXECUTE st;
  DEALLOCATE PREPARE st;
END;

 

One thought on “MySQL 5.x: passing limits as stored procedure / function parameters”

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image