Mysql procedure error

Question

Mysql procedure error

Navigation

#1 by (2 votes)

0

I'm trying to create a procedure but I'm getting errors, after a few tries, I've been able to solve some problems, but I'm not getting any.

Attempting to create 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 ;

mysql sql

asked by anonymous 23.05.2018 / 19:20

1 answer

Problems with thumbnails in list and grid mode with bootstrap Check if file has already been copied

score 2 · Accepted Answer

The end of WHILE is determined by 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 ;