CODEWITHSUNDEEP
mysqlstored-procedures
Justin
Justin

Reputation: 2372

Stored procedure in MySQL

I am running the following command in PHPMyAdmin:

DELIMITER #
CREATE PROCEDURE addid()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a,b FLOAT DEFAULT 0;
  DECLARE c,d INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT time FROM results;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
  read_loop: LOOP
    FETCH cur1 INTO a;
    IF done THEN
      LEAVE read_loop;
    IF a - b > 60 THEN 
      SET c = c+1;
    ELSE
      UPDATE results SET uid=c WHERE time=a;
    END IF;
    SET b = a;
  END LOOP;
CLOSE cur1;
END#
DELIMITER ;
CALL addid();

Maybe it will do what I want, maybe not. But I don't know because I can't call it.

I get the following error

#1064 - 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 'LOOP; CLOSE cur1; END# call addid()' at line 20

Sometime, depending on how I tweak it, I can get the create to run but then it cant find the stored procedure, like it just does not get made.

This is my first time ever using stored procedures so it's probably something stupid. I'm running MySQL 5.0.7.

Upvotes: 3

Views: 536

Answers (1)

Only You
Only You

Reputation: 2072

Just now I saw what seems to be the problem in your code....

IF done THEN
      LEAVE read_loop;
END IF;

The endif is missing.

Upvotes: 2

Related Questions