MySQL Stored Procedure Syntax Error: Finding Prime Numbers

Question

I'm trying to execute a MySQL stored procedure on HackerRank that finds prime numbers less than or equal to 1000 and prints them using a delimiter. However, I'm encountering a syntax error, and I'm not sure what's causing it. Here's my code:

DELIMITER //

CREATE PROCEDURE findPrimes()
BEGIN
    DECLARE n INT;
    DECLARE divisor INT;
    DECLARE isPrime BOOLEAN;
    
    SET n = 2;
    
    WHILE n <= 1000 DO
        SET divisor = 2;
        SET isPrime = TRUE;
        
        WHILE divisor * divisor <= n DO
            IF n % divisor = 0 THEN
                SET isPrime = FALSE;
                LEAVE;
            END IF;
            SET divisor = divisor + 1;
        END WHILE;
        
        IF isPrime THEN
            SET @result = CONCAT_WS('&', @result, n);
        END IF;
        SET n = n + 1;
    END WHILE;
    SELECT @result;
END;
//
DELIMITER ;

call findPrimes();

I receive the following error message:

ERROR 1064 (42000) at line 2: 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 ';
            END IF;
            SET divisor = divisor + 1;
        END WHILE;
' at line 16

Could someone please help me identify and rectify the syntax error in my code? Thank you.

Resolve the syntax errors!

Answer 1

Your LEAVE needs a label, in your case since you want to break the While you need to modify the While line from:

WHILE divisor * divisor <= n DO

to:

mylabel: WHILE divisor * divisor <= n DO

And provide the LEAVE a label so:

LEAVE mylabel;

Complete script:

CREATE PROCEDURE findPrimes()
BEGIN
    DECLARE n INT;
    DECLARE divisor INT;
    DECLARE isPrime BOOLEAN;
    
    SET n = 2;
    
    WHILE n <= 1000 DO
        SET divisor = 2;
        SET isPrime = TRUE;
        
        mylabel: WHILE divisor * divisor <= n DO
            IF n % divisor = 0 THEN
                SET isPrime = FALSE;
               LEAVE mylabel;
            END IF;
            SET divisor = divisor + 1;
        END WHILE;
        
        IF isPrime THEN
            SET @result = CONCAT_WS('&', @result, n);
        END IF;
        SET n = n + 1;
    END WHILE;
    SELECT @result;
END;

Call it:

call findPrimes();

Bonus track: I would do something much simpler using common table expression:

WITH RECURSIVE
    numbers AS (
        SELECT 2 as n
        UNION ALL
        SELECT n + 1 FROM numbers WHERE n < 1000
    ), 
    primes AS (
        SELECT n
        FROM numbers c
        WHERE NOT EXISTS (
            SELECT n FROM numbers c2 WHERE c.n > c2.n AND MOD(c.n, c2.n) = 0
        )
    )
SELECT REPLACE(GROUP_CONCAT(n), ',', '&') FROM primes