MySQL Forums
Forum List  »  InnoDB

using LIMIT clause in SELECT query of a CURSOR
Posted by: Iftekhar anam
Date: August 01, 2007 11:32PM

Hi,
I want need using LIMIT clause in a CURSOR as follws:
***************************************
DELIMITER $$

DROP PROCEDURE IF EXISTS `ilanddb`.`getComments` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getComments`(
in pk_iMetaId integer,
in startIndex integer,
in counts integer,
out output text
)
BEGIN
DECLARE done,noOfRows INT DEFAULT 0;
Declare userName varchar(100);
Declare sComment varchar(1000);
Declare dtDate datetime;

DECLARE cur1 CURSOR FOR
select fk_sUserName,sComment,dtCommentDate
from metaObjectComment
where fk_iMetaId=pk_iMetaId
limit counts offset startIndex;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

open cur1;
set output='';
set noOfRows=0;
REPEAT
FETCH cur1 INTO userName,sComment,dtDate;
IF NOT done THEN
set noOfRows = noOfRows +1;
select concat(output, '|',userName,'|',sComment,'|',dtDate,'|') into output;
end if;
UNTIL done END REPEAT;

close cur1;
END $$

DELIMITER ;
***************************************
But it shows the following error:
----------------------------------------------
Script line: 4 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 'counts offset startIndex;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET' at line 17
-----------------------------------------------

What is the worng in the above code?
OR
Is there any alternative way ?

Any suggestion would be highly appreciated..
Thanks...

Iftekhar

Options: ReplyQuote


Subject
Views
Written By
Posted
using LIMIT clause in SELECT query of a CURSOR
8952
August 01, 2007 11:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.