Error while creating procedure in Mysql 5.1

Question

I am executing below procedure in Mysql.

DROP procedure IF EXISTS `wm_batch_list`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` procedure `wm_batch_list`(IN p_start int(11),IN p_range int(11))
BEGIN
select * from batch ORDER BY start_year DESC limit p_start,p_range;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

but i am getting this error:

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p_start,p_range;
END' at line 3

Please suggest where i am doing mistake?

Answer 1

Googling a bit i found a question related to yours at passing LIMIT as parameters to MySQL sproc

From http://dev.mysql.com/doc/refman/5.1/en/select.html:

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

So the way to solve it is using a prepare statement

SET @skip=p_start;
SET @rows=p_range;

PREPARE STMT FROM 'SELECT * FROM table LIMIT ?, ?';
EXECUTE STMT USING @skip, @rows;