Procedure with cursor and prepared statement problem: You have an error in your SQL syntax; EXECUTE stmt; DEALLOCATE PREPARE stmt; END L' at line 34

Question

I've a procedure that works in 10.3.28-MariaDB, but there is some problem I've troubled to find in MySQL 5.7.26. The procedure takes all the procedures/functions, changed after some date and grants execute permissions on them to the specific user.

CREATE PROCEDURE ExecuteGrantsForModifiedProcedures(IN p_schema varchar(120),
                                                       IN p_mysqlUser varchar(120),
                                                       IN p_fromModificationDate datetime)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_statement TEXT;
    # Get all the modified procedures/functions
    DECLARE cur1 CURSOR FOR SELECT CONCAT('GRANT EXECUTE ON PROCEDURE `', ROUTINE_SCHEMA, '`.`', routine_name, '` TO ',
                                          p_mysqlUser, ';')
                            FROM information_schema.routines
                            where routine_schema = p_schema
                              AND ROUTINE_TYPE = 'PROCEDURE'
                              AND date(LAST_ALTERED) >= date(p_fromModificationDate)
                            UNION
                            SELECT CONCAT('GRANT EXECUTE ON FUNCTION `', ROUTINE_SCHEMA, '`.`', routine_name, '` TO ',
                                          p_mysqlUser, ';')
                            FROM information_schema.routines
                            where routine_schema = p_schema
                              AND ROUTINE_TYPE = 'FUNCTION'
                              AND date(LAST_ALTERED) >= date(p_fromModificationDate);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    SET max_sp_recursion_depth = 1024;

    OPEN cur1;

    read_loop:
    LOOP
        FETCH cur1 INTO v_statement;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        PREPARE stmt FROM v_statement;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur1;

end;

MySQL shows the error when I try to run the procedure create statement:

[1064] 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 'v_statement; EXECUTE stmt; DEALLOCATE PREPARE stmt; END L' at line 34

Answer 1

In MySQL only user-defined variables may be used in PREPARE. So remove v_statement declaration and use @v_statement everywhere instead:

CREATE PROCEDURE ExecuteGrantsForModifiedProcedures(IN p_schema varchar(120),
                                                       IN p_mysqlUser varchar(120),
                                                       IN p_fromModificationDate datetime)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_statement TEXT;
    # Get all the modified procedures/functions
    DECLARE cur1 CURSOR FOR SELECT CONCAT('GRANT EXECUTE ON PROCEDURE `', ROUTINE_SCHEMA, '`.`', routine_name, '` TO ',
                                          p_mysqlUser, ';')
                            FROM information_schema.routines
                            where routine_schema = p_schema
                              AND ROUTINE_TYPE = 'PROCEDURE'
                              AND date(LAST_ALTERED) >= date(p_fromModificationDate)
                            UNION
                            SELECT CONCAT('GRANT EXECUTE ON FUNCTION `', ROUTINE_SCHEMA, '`.`', routine_name, '` TO ',
                                          p_mysqlUser, ';')
                            FROM information_schema.routines
                            where routine_schema = p_schema
                              AND ROUTINE_TYPE = 'FUNCTION'
                              AND date(LAST_ALTERED) >= date(p_fromModificationDate);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    SET max_sp_recursion_depth = 1024;

    OPEN cur1;

    read_loop:
    LOOP
        FETCH cur1 INTO v_statement;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        SET @v_statement := v_statement;
        PREPARE stmt FROM @v_statement;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur1;

end