CODEWITHSUNDEEP
mysqlstored-procedureswhile-loop
Iqbal Khan
Iqbal Khan

Reputation: 363

End while loop missing semicolon

I created a simple stored procedure that loops through rows of one table and inserts them into another. For some reason the END WHILE loop is throwing a missing semicolon error. All the code looked right to me, and all the delimiters were set up right. I just can't figure out why it would be throwing these errors, googling this problem only pointed me to improperly used delimiter answers, but nothing more. Any help would be nice!

USE test;
DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO `test_2` (sku, qty) VALUES(sku, qty) FROM `test_dropship_upload` LIMIT i,1;
  SET i = i + 1;
END WHILE;

END $$

DELIMITER ;

Upvotes: 0

Views: 811

Answers (1)

Jocelyn
Jocelyn

Reputation: 11413

When I am stuck with a problem in a large block of code and can't find where the problem is, I usually split my code in smaller chunks and test them one at a time:

Test 1:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE testLoop()
BEGIN
END $$

DELIMITER ;

No errors: procedure declaration and use of delimiters is OK.

Test 2:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
END $$

DELIMITER ;

No errors: the declaration of variables within the procedure is OK.

Test 3:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
END $$

DELIMITER ;

No errors: the SELECT query and the variable assignment are OK.

Test 4:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
WHILE i<n DO 
  SET i = i + 1;
END WHILE;

END $$

DELIMITER ;

No errors: the WHILE loop is OK.

Test 5:

The only untested part is now the INSERT query:

INSERT INTO `test_2` (sku, qty) VALUES(sku, qty) FROM `test_dropship_upload` LIMIT i,1;

Looking a the documentation for INSERT and INSERT ... SELECT, we can see that your query is not valid: it is apparently missing a SELECT part and shouldn't have a VALUES part if you want to insert values from another table:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO `test_2` (sku, qty) SELECT sku, qty FROM `test_dropship_upload` LIMIT i, 1;
  SET i = i + 1;
END WHILE;

END $$

DELIMITER ;

The procedure creation now completes without errors.

Test 6:

However, you will get a syntax error on the SELECT query when executing the procedure: MySQL doesn't accept using LIMIT with a variable.
To make it work, you need to use a prepared statement. 

PREPARE stmt FROM "INSERT INTO `test_2` (sku, qty) SELECT sku, qty FROM `test_dropship_upload` LIMIT ?, 1";
EXECUTE stmt using @i;
DEALLOCATE PREPARE stmt;

It is also not allowed to used local variables in prepared statements:

Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. For example, SELECT ... INTO local_var cannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters.

To circumvent this problem, use a session variable @i instead of your local variable i:

Final version of the procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET @i=0;
WHILE @i<n DO
  PREPARE stmt FROM "INSERT INTO `test_2`(sku, qty) SELECT sku, qty FROM `test_dropship_upload` LIMIT ?, 1";
  EXECUTE stmt USING @i;
  DEALLOCATE PREPARE stmt;
  SET @i = @i + 1;
END WHILE;

END $$

DELIMITER ;

You can apply the same method to debug many complex programming problems: start with a simple version of your code, test it. If it works test again with a more code, if not locate and fix the errors before continuing.

Upvotes: 1

Related Questions