Syntax Error in MySQL Stored Procedure when declaring variables

Question

I am trying to create a procedure to automatically add some values to a table, based on a couple of other tables.

These are the tables I have:

ENROLMENT table (where I store the programmes the students are enrolled in):

+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| ENROLMENT_NR   | int(10) | NO   | PRI | NULL    | auto_increment |
| ENROLMENT_DATE | date    | NO   |     | NULL    |                |
| STUDENT_NR     | int(10) | NO   | MUL | NULL    |                |
| PROGRAMME_NR   | int(10) | NO   | MUL | NULL    |                |
+----------------+---------+------+-----+---------+----------------+

PROGRAMME_MODULE table (a Junction table which lists combinations of programmes and respective modules):

+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| PROGRAMME_NR | int(10) | NO   | PRI | NULL    |       |
| MODULE_NR    | int(10) | NO   | PRI | NULL    |       |
+--------------+---------+------+-----+---------+-------+

REGISTRATION table (where I want to store the students' registrations into the modules of each of the programmes they are enrolled in):

+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| REGISTRATION_NR   | int(10) | NO   | PRI | NULL    | auto_increment |
| REGISTRATION_DATE | date    | NO   |     | NULL    |                |
| MODULE_NR         | int(10) | NO   | MUL | NULL    |                |
| STUDENT_NR        | int(10) | NO   | MUL | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+

I've never done a procedure in MySQL before. I did some basic ones with PL/SQL only. Based on some research, I created this procedure:

DROP PROCEDURE IF EXISTS REGISTER_STUDENTS_PROC;
DELIMITER ;;
CREATE PROCEDURE REGISTER_STUDENTS_PROC()
DECLARE st_nr INT DEFAULT 0;
DECLARE prg_nr INT DEFAULT 0;
DECLARE enrl_date DATE DEFAULT '0000-00-00';
DECLARE mod_nr INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 0;
DECLARE ii INT DEFAULT 0;
DECLARE nn INT DEFAULT 0;
SELECT COUNT(*) FROM TABLE ENROLMENT INTO n;
SET i=0;
SELECT COUNT(*) FROM TABLE PROGRAMME_MODULE INTO nn;
SET ii=0;
BEGIN
WHILE i<n DO
    SELECT STUDENT_NR FROM TABLE ENROLMENT INTO st_nr;
    SELECT PROGRAMME_NR FROM TABLE ENROLMENT INTO prg_nr;
    SELECT ENROLMENT_DATE FROM TABLE ENROLMENT INTO enrl_date;
    BEGIN
    WHILE ii < n DO
        SELECT MODULE_NR FROM TABLE PROGRAMME_MODULE WHERE PROGRAMME_NR = prg_nr INTO mod_nr;
        INSERT INTO REGISTRATION (REGISTRATION_DATE,MODULE_NR,STUDENT_NR) VALUES (enrl_date,mod_nr,st_nr);
        SET ii = ii + 1;
    END WHILE;
    SET i = i + 1;
END WHILE;
END;
;;

This is the error I have:

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 'DECLARE st_nr INT DEFAULT 0;
DECLARE prg_nr INT DEFAULT 0; DECLARE enrl_date DAT' at line 2.

One difficulty I find with SQL is that some errors are very unspecific, which constitutes an added challenge to a beginner... It's probably something basic but I can't see it.

Thank you

You are right, how could I have missed that! Only now I have another error (ERROR 1064 (42000): (...) near 'TABLE ENROLMENT INTO n; SET i=0; SELECT COUNT(*) FROM TABLE PROGRAMME_MODULE INT' at line 11) ... Shall I write a separate question or just edit the one above ? — PrincessLilly
– PrincessLilly, Commented Mar 30, 2014 at 20:57
Remove TABLE keyword SELECT STUDENT_NR FROM ENROLMENT... from all queries. — Mihai
– Mihai, Commented Mar 30, 2014 at 20:58
There seems to be one last thing at the end: (...) near 'WHILE; END' ? — PrincessLilly
– PrincessLilly, Commented Mar 30, 2014 at 21:03
Same generic error I'm afraid: "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 'WHILE; END' at line 26" — PrincessLilly
– PrincessLilly, Commented Mar 30, 2014 at 21:05

PrincessLilly · Accepted Answer · 2014-03-30 23:38:42Z

Based on the comments from @Mihai, here is the code with no syntax errors:

DROP PROCEDURE IF EXISTS REGISTER_STUDENTS_PROC;
DELIMITER ;;
CREATE PROCEDURE REGISTER_STUDENTS_PROC()
BEGIN
DECLARE st_nr INT DEFAULT 0;
DECLARE prg_nr INT DEFAULT 0;
DECLARE enrl_date DATE DEFAULT '0000-00-00';
DECLARE mod_nr INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 0;
DECLARE ii INT DEFAULT 0;
DECLARE nn INT DEFAULT 0;
SELECT COUNT(*) FROM ENROLMENT INTO n;
SET i=0;
SELECT COUNT(*) FROM PROGRAMME_MODULE INTO nn;
SET ii=0;
WHILE i<n DO
    SELECT STUDENT_NR FROM ENROLMENT INTO st_nr;
    SELECT PROGRAMME_NR FROM ENROLMENT INTO prg_nr;
    SELECT ENROLMENT_DATE FROM ENROLMENT INTO enrl_date;
    BEGIN
    WHILE ii < n DO
        SELECT MODULE_NR FROM PROGRAMME_MODULE WHERE PROGRAMME_NR = prg_nr INTO mod_nr;
        INSERT INTO REGISTRATION (REGISTRATION_DATE,MODULE_NR,STUDENT_NR) VALUES (enrl_date,mod_nr,st_nr);
        SET ii = ii + 1;
    END WHILE;
    END;
    SET i = i + 1;
END WHILE;
END;
;;

So, there were three corrections:

1- Variables must be declared after BEGIN; 2- All BEGIN statements must be closed with END (the nested one wasn't closed); 3- Must use SELECT FROM <<TABLE_NAME>> and not SELECT FROM TABLE <<TABLE_NAME>>.

Collectives™ on Stack Overflow

Syntax Error in MySQL Stored Procedure when declaring variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related