CODEWITHSUNDEEP
mysqlstored-procedures
TheOnlyIdiot
TheOnlyIdiot

Reputation: 1202

mysql : What is wrong with my stored procedure?

Its my first time to learn stored procedure. I have here below : 

DELIMETER //
DROP PROCEDURE IF EXISTS RepeatTomProc //
CREATE PROCEDURE RepeatTomProc()
BEGIN
    DECLARE x  INTEGER;
    SET x = 1;
    REPEAT
            INSERT INTO TOM VALUES(x);
            SET  x = x + 1;
    UNTIL x  > 100
    END REPEAT;    
END //
DELIMITER;

What I Want to happen is that when the loop is not yet done it will insert the x value into the table TOM.

As for now the table TOM is empty.

for the table : 

CREATE TABLE TOM (
    TOM_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (TOM_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My problem is when I try to create the stored procedure by copying and pasting the code in my mysql terminal I have the following error : 

mysql> DELIMETER $$
-> DROP PROCEDURE IF EXISTS RepeatTomProc $$
-> CREATE PROCEDURE RepeatTomProc()
-> BEGIN
-> DECLARE x INT;
ERROR 1064 (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 'DELIMETER $$
DROP PROCEDURE IF EXISTS RepeatTomProc $$
CREATE PROCEDURE RepeatTo' at line 1
mysql> SET x = 1;
ERROR 1193 (HY000): Unknown system variable 'x'
mysql> REPEAT
->
Display all 748 possibilities? (y or n)
?                           DESC                        ITERATE                     ORD 
 // It displays lots of other possibilities which I have ommitted                                             
-> = x + 1;
ERROR 1064 (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 'REPEAT
= x + 1' at line 1
mysql> UNTIL x  > 100
-> END REPEAT;    
ERROR 1064 (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 'UNTIL x  > 100
END REPEAT' at line 1
mysql> END $$
-> DELIMITER;

I have based my codes from : Loop in Stored Procedures

Upvotes: 1

Views: 570

Answers (1)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

You mistyped DELIMITER

try this

DELIMITER //
DROP PROCEDURE IF EXISTS RepeatTomProc //
CREATE PROCEDURE RepeatTomProc()
BEGIN
    DECLARE x  INTEGER;
    SET x = 1;
    REPEAT
            INSERT INTO TOM VALUES(x);
            SET  x = x + 1;
    UNTIL x  > 100
    END REPEAT;    
END //
DELIMITER ;

Also you need space between DELIMITER and ; in the end

Upvotes: 3

Related Questions