Error while creating MySQL stored procedure

Question

create procedure proc_Grade(in roll int(5),in name varchar(20),in marks int(5))
-> begin
-> declare grade varchar(20);
-> insert into student values(roll,name,marks);
-> if marks<=100 and marks>=67 then set grade="distinction";
-> if marks<=66 and marks>=57 then set grade="firstclass";
-> if marks<=56 and marks>=47 then set grade="secdclass";
-> if marks<=46 and marks>=40 then set grade="pass";
-> else set grade="fail";
-> insert into result values(roll,grade);
-> end if;
-> end$$

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 '' at line 12

Here's a screenshot of command line client

Are you sure you want to add else set grade="fail"? it will set "fail" on more records then you expect. — Jeroen Heier
– Jeroen Heier, Commented Oct 27, 2018 at 6:05
@kunaldeshpande: you have to execute delimiter $$ before create procedure statement? can you post screenshot of your mysql client with error stack displayed. — Ravinder Reddy
– Ravinder Reddy, Commented Oct 27, 2018 at 6:25

Paul Campbell · Accepted Answer · 2018-10-27 19:07:12Z

1

As error messages go, that one is particularly unhelpful.

There are two remaining issues:

The position of the second insert statement (result) which will only run for students who 'fail' the course.
The IF statement syntax where the second, and subsequent, IF should be ELSEIF or you should terminate each IF statement with END IF;

The syntax should be in the form

IF condition1 THEN
  statement1;
ELSEIF condition2 THEN
  statement2;
END IF;

The documentation is at https://dev.mysql.com/doc/refman/5.7/en/if.html (though watch out for the typo which misses the semi-colon after END IF;)

Your procedure would look like this:

delimiter $$

create procedure proc_Grade(in roll int(5),in name varchar(20),in marks int(5))
begin
  declare grade varchar(20);

  insert into student values(roll,name,marks);

  if marks<=100 and marks>=67 then 
    set grade="distinction";
  elseif marks<=66 and marks>=57 then
    set grade="firstclass";
  elseif marks<=56 and marks>=47 then
    set grade="secdclass";
  elseif marks<=46 and marks>=40 then
    set grade="pass";
  else
    set grade="fail";
  end if;

  insert into result values(roll,grade);

end $$

answered Oct 27, 2018 at 19:07

Paul Campbell

1,9862 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael - sqlbot Over a year ago

The error message is a MySQL quirk but still helpful, if you know what it means: unexpected thing found next in the parse buffer: ...near 'unexpected thing' at line.... An empty string in the error means there is nothing left to parse, i.e. the parser ran out of tokens in a state when it simply can't be possible that your statement is complete. Here, the parser expected the last END to be followed by IF, not nothing. (The delimiter is already not in the parse buffer because the CLI actually strips it.) SELECT * FROM; or SELECT * FROM table1 WHERE; would generate the same error.

Paul Campbell Over a year ago

@Michael-sqlbot, thanks for that contribution, a very clear explanation.

Collectives™ on Stack Overflow

Error while creating MySQL stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related