MySQL error: 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

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

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 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

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 4

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

Answer 5

MYSQL PROCEDURE steps:

1. change delimiter from default ' ; ' to ' // '

DELIMITER //

2. create PROCEDURE, you can refer syntax

   NOTE: Don't forget to end statement with ' ; '

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

3. Change delimiter back to ' ; '

delimiter ;

4. Now to execute:

call ProG();

Answer 6

I got the same error below:

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 3

When using a trailing comma as shown below:

create table person(
  name varchar(50),
);             -- ↑ A trailing comma

So, I removed the trailing comma as shown below, then the error was solved:

create table person(
  name varchar(50)
);             -- ↑ No trailing comma

And, I also got the same error below:

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 'count( num int )' at line 1

When there is no space between "count" and "(" as shown below because it's recognized as "count()" which is the built-in function in MySQL:

            -- No space
                 ↓
create table count(
  num int
);

So, I made a space between "count" and "(" as shown below, then the error was solved:

          -- Make a space
                  ↓
create table count (
  num int
);