CODEWITHSUNDEEP
mysqlsqlstored-procedures
ThisaruG
ThisaruG

Reputation: 3412

Whats the error in this mysql syntax?

I am pretty new forthe world of mysql. I'm trying to create a procedure to decide the class of a student by his/her GPA. I gave them a CRegNo which is going 1 to 10 at the time. here's my code:

mysql> delimiter //
 mysql> CREATE PROCEDURE DecideClass ()
    -> BEGIN
    -> DECLARE count INT;
    -> DECLARE max INT;
    -> SET count = 1;
    -> SET max = 10;
    -> DECLARE FClass CHAR(18) DEFAULT 'First class honors';
    -> DECLARE sUpper CHAR(34) DEFAULT 'Second class honors-upper division';
    -> DECLARE sLower CHAR(34) DEFAULT 'Second class honors-lower division';
    -> DECLARE tClass CHAR(18) DEFAULT 'Third class honors';
    -> WHILE count <= max DO
    -> DECLARE GPA DECIMAL(3,2);
    -> SET GPA = (SELECT GPA FROM STUDENT WHERE CRegNo = count);
    -> IF GPA >= 3.7 THEN
    -> UPDATE STUDENT SET Class = FClass WHERE CRegNo = count;
    -> ELSE IF GPA < 3.7 AND GPA >= 3.3 THEN
    -> UPDATE STUDENT SET Class = SUpper WHERE CRegNo = count;
    -> ELSE IF GPA < 3.3 AND GPA >= 2.7 THEN
    -> UPDATE STUDENT SET Class = SLower WHERE CRegNo = count;
    -> ELSE IF GPA < 2.7 AND GPA >= 2.0 THEN
    -> UPDATE STUDENT SET Class = TClass WHERE CRegNo = count;
    -> END IF
    -> SET count = count + 1;
    -> END WHILE;
    -> END;
    -> //

Then it says that

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 FClass CHAR(18) DEFAULT 'First class honors'; DECLARE sUpper CHAR(34) DE' at line 7

Can anyone help me out here ? whats the error ?

Upvotes: 0

Views: 78

Answers (2)

Hammy
Hammy

Reputation: 86

Declarations have to be right after a BEGIN block. In your case just move the -> DECLARE FClass CHAR(18) DEFAULT 'First class honors'; -> DECLARE sUpper CHAR(34) DEFAULT 'Second class honors-upper division'; -> DECLARE sLower CHAR(34) DEFAULT 'Second class honors-lower division'; -> DECLARE tClass CHAR(18) DEFAULT 'Third class honors'; and DECLARE GPA = DECIMAL; before the -> SET count = 1; -> SET max = 10;

Upvotes: 1

Dan
Dan

Reputation: 11104

All of your DECLARE statements must be at the beginning, before any SETs or anything else.

Upvotes: 3

Related Questions