CODEWITHSUNDEEP
mysql
TSCAmerica.com
TSCAmerica.com

Reputation: 5377

MYSQL Cursor Implementation Question

I am using following Stored procedure in which the following cursor code is written ( i am using PHPmyadmin to execute the code

create procedure cursorproc(OUT p_out DECIMAL(10,2))
begin

   declare l_salary, l_total DECIMAL(10,2);

   declare cur_1 cursor for select line_distance from elements;

   open cur_1;

   set l_total = 0;

   loop

      fetch cur_1 into l_salary;


         set l_total = l_total + l_salary;

  end loop;

   close cur_1;

   set p_out = l_total;

end;

And the table schema:

CREATE TABLE IF NOT EXISTS `elements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `distance` int(11) NOT NULL,
  `repeat` int(2) NOT NULL,
  `interval` varchar(11) NOT NULL,
  `eta` varchar(11) NOT NULL,
  `best` varchar(11) NOT NULL,
  `line_distance` int(5) NOT NULL,
  `line_time` varchar(11) NOT NULL,
  `intensity` varchar(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `best_time_event` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=215 ;

I am getting the following error:

ERROR -  #1329 - No data - zero rows fetched, selected, or processed

What can be the problem ? please help

Upvotes: 0

Views: 1024

Answers (2)

Kevin Burton
Kevin Burton

Reputation: 11936

you could catch the error DECLARE CONTINUE HANDLER FOR NOT FOUND

see: http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html

or you can count the number of rows the sql will select and choose not to read them if there are none

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30775

I do not know much about MySQL, but the error message is pretty clear - you never check whether your fetch actually returns data / reaches EOF, so you try to fetch even if your query returns no more data. Search the MySQL documentation for info about "CONTINUE HANDLER".

The more important question is: Why do you use a stored procedure at all for this? And why - if you have to - do you use an explicit loop where a simple

select sum(line_distance) from elements

should suffice?

Upvotes: 1

Related Questions