0

I am trying to create a simple stored procedure in MYSQL 5.6.13

create procedure create_logger_id(IN mobile BIGINT)
BEGIN
  SELECT COUNT(*) INTO mobile FROM USER_REG_TYPE;
END

But Get this 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 '' at line 3

I even tried delimiter //, DECLARE mobile INT and few others all of them lead to the same error.

The MYSQL server is hosted on AWS RDS (Added this info to understand if there is a known issue)

Update: if I try

DELIMITER //;
CREATE PROCEDURE create_logger_id(IN mobile BIGINT)
BEGIN
  SELECT COUNT(*) INTO mobile FROM USER_REG_TYPE;
END //
DELIMITER ;

I get the following 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 'DELIMITER //' at line 1
Nikhil Varma
  • 37
  • 5
  • 1
    What do you mean by "I even tried delimiter //"? You need the delimiter. Add it to your question, you may have used it incorrectly. – Solarflare Aug 16 '18 at 10:09
  • 1
    Shouldn't that parameter be declared as OUT mobile BIGINT if you want to get the COUNT() of USER_REG_TYPE – Paul Campbell Aug 16 '18 at 12:36

1 Answers1

1

You need to add a delimiter.

DELIMITER //
CREATE PROCEDURE create_logger_id(IN mobile BIGINT)
BEGIN
  SELECT COUNT(*) INTO mobile FROM USER_REG_TYPE;
END //
DELIMITER ;

I'd like to encourage you to read more on delimiters: Delimiters in MySQL

Ildar Akhmetov
  • 1,331
  • 13
  • 22
  • `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 'DELIMITER //' at line 1` this is the error it throws – Nikhil Varma Aug 16 '18 at 10:24
  • 1
    @NikhilVarma `DELIMITER` is not a MySQL command. It's a command that your MySQL client needs to support. Which client are you using? – Ildar Akhmetov Aug 16 '18 at 10:27
  • 1
    @NikhilVarma Which version? – Ildar Akhmetov Aug 16 '18 at 10:32
  • @IIdar Build 112, Please note its [Workbench/J](https://www.sql-workbench.eu/) and Not mysql workbench. I also use SQLYog. You can provide a resolution in either of these. Update :DELIMITER// is SQLYog worked – Nikhil Varma Aug 16 '18 at 10:39
  • @NikhilVarma Sounds like it's a problem with Workbench/J. Just looked at their docs, and their MySQL connector is somewhat weird. – Ildar Akhmetov Aug 16 '18 at 10:45