MySql stored procedure no data fetched

279 Views Asked by At

SQLFiddle

I need to loop throught my cursor and insert new values in another table.

I have two tables:

CREATE TABLE peoples 
    (
 id int auto_increment primary key, 
 name varchar(20), 
 enabled varchar(1)
 )
/

INSERT INTO peoples
(name, enabled)
VALUES
('john', 'F'),
('jane', 'T')
/

CREATE TABLE test 
    (
 id int, 
 logins int
 )
/

I need to select all peoples enabled = T and insert a new entry in my second table 'test'. I created a stored procedure:

 CREATE PROCEDURE sp_a()
BEGIN
DECLARE p int;
DECLARE done INT DEFAULT FALSE;
DECLARE peoples_cur CURSOR FOR select p.id from peoples p where p.enabled='T';
OPEN peoples_cur;
REPEAT
      FETCH peoples_cur INTO p;
      IF NOT done THEN
      INSERT INTO test
        (id, logins)
        VALUES
        (p, '999');
      END IF;
  UNTIL done END REPEAT;
CLOSE peoples_cur;
END;
/

But i got the error:

No data - zero rows fetched, selected, or processed: CALL sp_a()

SQLFiddle

1

There are 1 best solutions below

2
On BEST ANSWER

Simple way:

DROP PROCEDURE IF EXISTS sp_a;

DELIMITER $$
CREATE PROCEDURE `sp_a`()
BEGIN
 INSERT INTO test (logins)
 SELECT COUNT(id) AS l  FROM peoples WHERE enabled = 'T';
END$$

CALL sp_a();