Procedure in MySQL with LOOP

Question

I'm really struggling with this LOOP in my procedure. I can't understand what the error means. I guess anyone can just try to run this without any knowledge of the database...

DROP PROCEDURE IF EXISTS removeDuplicates;
DELIMITER $$
CREATE PROCEDURE removeDuplicates(str TEXT) 
BEGIN
    DECLARE temp_word TEXT;
    DECLARE last_word TEXT;
    DECLARE result TEXT;
    DECLARE finished INT DEFAULT false;
    DECLARE words_cursor CURSOR FOR
        SELECT word FROM explosion;
    DECLARE CONTINUE handler FOR NOT found
        SET finished = true;

    CALL explode(str);
    DROP TABLE IF EXISTS temp_words;
    CREATE TABLE temp_words (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, t VARCHAR(100));

    OPEN words_cursor;
    loop_words: LOOP
        FETCH words_cursor INTO temp_word;
        IF finished THEN
            LEAVE loop_words;
        IF temp_word = "" THEN
            SET result = CONCAT(result, temp_word, " ");
            SET last_word = temp_word;
        ELSEIF last_word = temp_word THEN
            SET last_word = temp_word;
        ELSE 
            INSERT INTO temp_words (t) VALUES (temp_word);
        END IF;
    END LOOP loop_words;
    CLOSE words_cursor;
    RETURN result;
END$$

DELIMITER ;

Any pointers would be gratefully received. The error I am getting is:

[ERROR in query 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 'LOOP loop_words; CLOSE words_cursor; RETURN result; END' at line 29

I'm sure it must be something simple, but I'll be damned if I can work it out.

Thanks, Chris

Answer 1

    DROP PROCEDURE IF EXISTS removeDuplicates;
    DELIMITER $$
    CREATE PROCEDURE removeDuplicates(str TEXT , OUT OUT_STR_RESULT TEXT) 
    BEGIN
        DECLARE temp_word TEXT;
        DECLARE last_word TEXT;
        DECLARE result TEXT;
        DECLARE finished INT DEFAULT false;
        DECLARE words_cursor CURSOR FOR
            SELECT word FROM explosion;
        DECLARE CONTINUE handler FOR NOT found
            SET finished = true;

        CALL explode(str);
        DROP TABLE IF EXISTS temp_words;
        CREATE TABLE temp_words (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, t VARCHAR(100));

        OPEN words_cursor;
        loop_words: LOOP
            FETCH words_cursor INTO temp_word;
            IF finished THEN
                LEAVE loop_words;
                IF temp_word = "" THEN
                    SET result = CONCAT(result, temp_word, " ");
                    SET last_word = temp_word;
                ELSEIF last_word = temp_word THEN
                    SET last_word = temp_word;
                ELSE 
                    INSERT INTO temp_words (t) VALUES (temp_word);
                END IF;
            END IF; --  i had made changes at this line.
        END LOOP loop_words;
        CLOSE words_cursor;
        SET OUT_STR_RESULT =result;
--         RETURN result;
    END$$
;

You missed one END IF.

One more thing: you can't put RETURN statement in procedure; it's only allowed in FUNCTION. Instead of that you can use OUT parameter for same.

Try above code.