MySQL error: You have an error in your SQL syntax;

Question

I have the Stored procedure like this:

CREATE PROCEDURE ProG()
  BEGIN
    SELECT * FROM `hs_hr_employee_leave_quota`;
  END

But it gives the 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 '' at line 3

What does the error mean? What is wrong with line number 2?

Answer 1

Delimiters, delimiters...

You really need them when there are multiple statements in your procedure. (in other words, do you have a ; in your code and then more statements/commands? Then, you need to use delimiters).

For such a simpler rpocedure as yours though, you could just do:

CREATE PROCEDURE ProG()
  SELECT * FROM `hs_hr_employee_leave_quota`;

Answer 2

How to find out what this MySQL Error is trying to say:

#1064 - You have an error in your SQL syntax;

This error has no clues in it. You have to double check all of these items to see where your mistake is:

1. You have omitted, or included an unnecessary symbol: !@#$%^&*()-_=+[]{}\|;:'",<>/?
2. A misplaced, missing or unnecessary keyword: select, into, or countless others.
3. You have unicode characters that look like ascii characters in your query but are not recognized.
4. Misplaced, missing or unnecessary whitespace or newlines between keywords.
5. Unmatched single quotes, double quotes, parenthesis or braces.

Take away as much as you can from the broken query until it starts working. And then use PostgreSQL next time that has a sane syntax reporting system.

Answer 3

You have to change delimiter before using triggers, stored procedures and so on.

delimiter //
create procedure ProG() 
begin 
SELECT * FROM hs_hr_employee_leave_quota;
end;//
delimiter ;

Answer 4

This might be a memmory issue on mysql try to increase max_allowed_packet in my.ini