Syntax error in local variable inside trigger declaration

Question

I have the following store procedure

CREATE PROCEDURE `update_ordenes`()
BEGIN
    DECLARE record CURSOR FOR 
        SELECT ordenes.idorden, ordenes.idafiliado 
            FROM ordenes, afiliados 
            WHERE afiliados.idafiliado = ordenes.idafiliado;

    OPEN record;
    REPEAT
        FETCH record INTO @id_orden, @id_afil_viejo;

        INSERT INTO afil2(nombre, apellido, documento) 
            (SELECT nombre, apellido, documento
                FROM afiliados 
                WHERE idafiliado = @id_afil_viejo);

        SET @last_id = (SELECT id FROM afil2 ORDER BY id DESC LIMIT 1);

        UPDATE ordenes 
        SET afil2 = @last_id,
            ordenes.idafiliado = NULL
        WHERE ordenes.idafiliado = @id_afil_viejo
              AND ordenes.idorden = @orden_id;
    UNTIL done END REPEAT;
END

for some reason, I get an syntax error at line FETCH record INTO @id_orden, @id_afil_viejo;

in mysql documentation I can run SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;. Is the problem in the use of the FETCH? or What is the problem?

Answer 1

FETCH (which grabs the next record from, and advances, a cursor) is not the same as SELECT ... INTO (which only works with queries that returns a single record).

FETCH can only fetch into local variables (which have no @ prefix but must be DECLAREd at the start of your stored program), whereas SELECT ... INTO can also fetch into user-defined variables (which do have a @ prefix and are not DECLAREd).

You must therefore DECLARE a local variable into which your records will be FETCHed:

CREATE PROCEDURE `update_ordenes`()
BEGIN

    DECLARE id_orden BIGINT UNSIGNED DEFAULT 5;  -- as appropriate
    DECLARE id_afiv_viejo VARCHAR(15) NOT NULL;  -- as appropriate

    DECLARE record CURSOR FOR 
        SELECT ordenes.idorden, ordenes.idafiliado 
            FROM ordenes, afiliados 
            WHERE afiliados.idafiliado = ordenes.idafiliado;

    OPEN record;
    REPEAT
        FETCH record INTO id_orden, id_afil_viejo;

        INSERT INTO afil2(nombre, apellido, documento) 
            (SELECT nombre, apellido, documento
                FROM afiliados 
                WHERE idafiliado = id_afil_viejo);

        SET @last_id = (SELECT id FROM afil2 ORDER BY id DESC LIMIT 1);

        UPDATE ordenes 
        SET afil2 = @last_id,
            ordenes.idafiliado = NULL
        WHERE ordenes.idafiliado = id_afil_viejo
              AND ordenes.idorden = id_orden;
    UNTIL done END REPEAT;
END

All that said, your sproc is very unsafe (a race hazard exists between the first INSERT statement and the SET that follows) and an incredibly unwieldy way of doing something that ought to be much simpler.