Mysql Process Error

Question

I’m trying to create a procedure but I’m getting errors, after a few attempts, I managed to solve some problems, but I’m not getting more.

When trying to create me generates error

> ERROR 1064 (42000): You have an error in your SQL sintaxe; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near '' at line 43

DELIMITER 
CREATE PROCEDURE SP_INS_EXT_ON_OFF_01()
BEGIN
DECLARE ramal VARCHAR(30);
DECLARE status VARCHAR(30);
DECLARE ramal_cont INT;
DECLARE status_cont INT;
DECLARE limite VARCHAR(6);
DECLARE limite_INT INT;
DECLARE total INT;
DECLARE contador INT;
DECLARE aux INT;
DECLARE aux2 INT;

SET `aux2` = 1;
SET `aux` = 8;
SET `contador` = 0;
SET `ramal_cont` = 10;
SET `status_cont` = 17;

SELECT `total`= COUNT(*) FROM `temp_log_users`;
SET `total` = `total` - 13;
SELECT `limite` = `text` FROM `temp_log_users` WHERE `temp_log_users_id` = `total`;

SET `limite_INT` = CAST(`limite` AS DECIMAL);

while `contador` <= `limite` DO
BEGIN
SELECT `ramal`= `texto` FROM `temp_log_users` WHERE `temp_log_users_id` = `ramal_cont`;
SELECT `status`= `texto` FROM `temp_log_users` WHERE `temp_log_users_id` = `status_cont`;

IF `status` = 'OK' THEN UPDATE `users` SET `tab_status_online_id` = 1 WHERE `extension` = `ramal`;
ELSE UPDATE `users` SET `tab_status_online_id` = 0 WHERE `extension` = `ramal` AND `tab_status_online_id` = 1;
END IF;

SET `ramal_cont` = `ramal_cont` + `aux`;
SET `status_cont` = `status_cont` + `aux`;

SET `contador` = `contador` + `aux2`;
END;

DELETE FROM `temp_log_users`;
ALTER TABLE `temp_log_users` AUTO_INCREMENT  = 1;
END;
DELIMITER ;

Answer 1

The end of WHILE is determined by the END WHILE; and not only by END;

DELIMITER $$
CREATE PROCEDURE SP_INS_EXT_ON_OFF_01()
BEGIN
    DECLARE ramal VARCHAR(30);
    DECLARE STATUS VARCHAR(30);
    DECLARE ramal_cont INT;
    DECLARE status_cont INT;
    DECLARE limite VARCHAR(6);
    DECLARE limite_INT INT;
    DECLARE total INT;
    DECLARE contador INT;
    DECLARE aux INT;
    DECLARE aux2 INT;

    SET `aux2` = 1;
    SET `aux` = 8;
    SET `contador` = 0;
    SET `ramal_cont` = 10;
    SET `status_cont` = 17;

    SELECT `total`= COUNT(*) FROM `temp_log_users`;
    SET `total` = `total` - 13;
    SELECT `limite` = `text` FROM `temp_log_users` WHERE `temp_log_users_id` = `total`;

    SET `limite_INT` = CAST(`limite` AS DECIMAL);

    WHILE `contador` <= `limite` DO
        SELECT `ramal`= `texto` FROM `temp_log_users` WHERE `temp_log_users_id` = `ramal_cont`;
        SELECT `status`= `texto` FROM `temp_log_users` WHERE `temp_log_users_id` = `status_cont`;

        IF `status` = 'OK' THEN
            UPDATE `users` SET `tab_status_online_id` = 1 WHERE `extension` = `ramal`;
        ELSE
            UPDATE `users` SET `tab_status_online_id` = 0 WHERE `extension` = `ramal` AND `tab_status_online_id` = 1;
        END IF;

        SET `ramal_cont` = `ramal_cont` + `aux`;
        SET `status_cont` = `status_cont` + `aux`;

        SET `contador` = `contador` + `aux2`;
    END WHILE;

    DELETE FROM `temp_log_users`;
    ALTER TABLE `temp_log_users` AUTO_INCREMENT  = 1;
END $$
DELIMITER ;