I am getting error when creating stored procedure with while loop in mysql

Question

i have created one stored procedure for sum of perticular condition but getting syntax error.

create table script :

CREATE TABLE count_smaller_coverage (count_records INT(11) ,block_id INT(11))

insert data :

INSERT INTO count_smaller_coverage 
SELECT '114000','1' UNION
SELECT '112000','2' UNION 
SELECT '98765','3' UNION 
SELECT '78965','4' UNION 
SELECT '4125','5' UNION 
SELECT '123654','6' UNION 
SELECT '78999','7' UNION 
SELECT '89888','8' UNION 
SELECT '99654','9' UNION 
SELECT '75365','10' UNION 
SELECT '25638','11' UNION 
SELECT '85236','12' UNION 
SELECT '65478','13' UNION 
SELECT '65478','14' UNION 
SELECT '85236','15' 

Stored Procedure :

DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
CREATE PROCEDURE test_mysql_while_loop()
BEGIN
DECLARE strat INT;
DECLARE END INT;
DECLARE SumofCount BIGINT;
DECLARE block_id VARCHAR(2000);
SET strat=(SELECT MIN(block_id) FROM count_smaller_coverage);
SET END =(SELECT MAX(block_id) FROM count_smaller_coverage);
CREATE TABLE blocks_parts (block_id VARCHAR(2000), Counts BIGINT);

test: WHILE strat<=END DO

BEGIN

IF SumofCount > 800000 THEN
SET SumofCount=0;
SET block_id = NULL;
END IF;

SET SumofCount=COALESCE(SumofCount,0)+(SELECT count_records FROM count_smaller_coverage WHERE block_id=strat);
SELECT block_id = (COALESCE(block_id + ',', '') + CAST(block_id AS CHAR)) AS id FROM count_smaller_coverage WHERE block_id=strat;

IF SumofCount BETWEEN 800000 AND 1000000 THEN
INSERT INTO blocks_parts(block_id,Counts) VALUES (block_id,SumofCount);
END IF;

IF SumofCount BETWEEN 800000 AND 100000 THEN
    LEAVE test;
END IF;

SET strat=strat+1;

END test;
END$$
DELIMITER ;

Error :

Query: CREATE PROCEDURE test_mysql_while_loop() BEGIN DECLARE strat INT; DECLARE end INT; DECLARE SumofCount BIGINT; DECLARE block_id V...

Error Code: 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 'test; END' at line 33

Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.060 sec

Answer 1

You have a couple of issues in your procedure. Firstly, you have an unnecessary BEGIN after the DO in your WHILE statement. You can either remove that or match it with an END. Secondly, you need to end the WHILE loop with an END WHILE, in your case adding the test label to that statement. This should work:

test: WHILE strat<=END DO
    -- BEGIN -- if you put BEGIN here ...
    IF SumofCount > 800000 THEN
    SET SumofCount=0;
    SET block_id = NULL;
    END IF;

    SET SumofCount=COALESCE(SumofCount,0)+(SELECT count_records FROM count_smaller_coverage WHERE block_id=strat);
    SELECT block_id = (COALESCE(block_id + ',', '') + CAST(block_id AS CHAR)) AS id FROM count_smaller_coverage WHERE block_id=strat;

    IF SumofCount BETWEEN 800000 AND 1000000 THEN
    INSERT INTO blocks_parts(block_id,Counts) VALUES (block_id,SumofCount);
    END IF;

    IF SumofCount BETWEEN 800000 AND 100000 THEN
        LEAVE test;
    END IF;

    SET strat=strat+1;
    -- END -- ... you must put END here
END WHILE test;

Answer 2

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `Test`$$

CREATE DEFINER=`root`@`%` PROCEDURE `Test`()

BEGIN

SET @SumofCount=0;
SET @block_id='';
SET @Start=(SELECT MIN(block_id) FROM count_smaller_coverage);
SET @End =(SELECT MAX(block_id) FROM count_smaller_coverage);
SET @v1=5;

myloop: WHILE @Start<=@End DO
IF @SumofCount > 800000 THEN
SET @SumofCount=0;
END IF;
SET @SumofCount=(IFNULL(@SumofCount,0)+(SELECT count_records FROM count_smaller_coverage WHERE block_id=@Start));
SET @block_id = (SELECT CONCAT(@block_id ,CAST(block_id AS CHAR),',') AS id FROM count_smaller_coverage WHERE block_id=@Start);

IF @SumofCount BETWEEN 800000 AND 1000000 THEN
SET @block_id = CONCAT(LEFT(@block_id, CHAR_LENGTH(@block_id) -1), '');
INSERT INTO blocks_parts(block_id,Counts) VALUES (@block_id,@SumofCount);
SET @block_id='';
END IF;
IF @Start = @End THEN
SET @block_id = CONCAT(LEFT(@block_id, CHAR_LENGTH(@block_id) -1), '');
INSERT INTO blocks_parts(block_id,Counts) VALUES (@block_id,@SumofCount);
END IF;

SET @Start=@Start+1;

END WHILE myloop;



    END$$

DELIMITER ;