0

Here is my Database server information

Server type: MySQL Server version: 5.7.16 - MySQL Community Server (GPL) Protocol version: 10

I run below code and got a error messate

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

I got an error messate: #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
Van Der Cong
  • 171
  • 2
  • 13
  • The rules for writing stored programs can be found https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html – P.Salmon Jun 20 '19 at 07:07

3 Answers3

3

You need to change the deliminator temporary because in procedure deliminator semicolon ; is used for statement end. if we did not change deliminator SQL consider procedure is ended in between and it does not compile successfully.

so before start the procedure change deliminator $$ and end of procedure revert the original deliminator ;

Try below code using Mysql Command line

DELIMITER $$
DROP PROCEDURE IF EXISTS dowhile$$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
SET v1 = v1 - 1;
END WHILE;
END$$
DELIMITER ;

After that you call procedure using 

call dowhile();
Mangesh Auti
  • 1,123
  • 1
  • 7
  • 12
1

Try this:

DELIMITER $

CREATE PROCEDURE dowhile()

BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

DELIMITER ;

You need to temporarily change the delimiter, so that MySQL can accept a sequence of statements, rather than stop after the first semi-colon.

Ricky McMaster
  • 4,289
  • 2
  • 24
  • 23
  • #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 'DELIMITER' at line 11 – Van Der Cong Jun 20 '19 at 04:21
  • 1
    What querying tool are you using? I ran this on DataGrip and it works absolutely fine. After the SET, I even added SELECT v1; so that I could verify the output. – Ricky McMaster Jun 21 '19 at 11:02
0
DELIMITER //

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END
//

DELIMITER ;
fifonik
  • 1,556
  • 1
  • 10
  • 18
  • No. can not ```There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. ERROR: Unknown Punctuation String @ 11 STR: // SQL: DELIMITER //``` – Van Der Cong Jun 20 '19 at 03:46
  • It was correct code (I checked it before posting). Other answers were the same (with different delimiter). So probably something was with your client. – fifonik Jun 23 '19 at 21:49