Ads

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

February 3, 2009
Filed under: Featured, MySQL 5.1, MySql, MySql 5.0 

 

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;

 

Tags: , , ,

Comments

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

  1. web development company bangalore on Mon, 9th May 2011 7:54 am

    2. good article, Thanks for sharing the programming concept.

Tell me what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!





*
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.
Click to hear an audio file of the anti-spam word

Subscribe without commenting