MySQL8 Procedure - Use Parameters in Cursor

Question

I'm trying to use input parameters of a stored procedure within a cursor of the procedure. Calling the procedure as follows results in an Error

--                               -role-      -table-  -cond- 
CALL grantRoleToUsersFromWhere('Student', 'studenten', true);

Error Code: 1146. Table 'uni4.utable' doesn't exist

This tells me that the parameter 'userTable' was not written to the variable 'uTable' OR 'uTable' is not recognized as a variable at all by the cursor Statement.

I tried different approaches storing / using the parameters. e.g. use them directly or store them in a Variable with a SET statement. However, if I try to use SET uTable=userTable; before the cursor declaration, MySQL WorkBench won't accept the Procedure declaration.

I spent quite some time on this but I think I'm missing an important yet simple part :-)

DROP PROCEDURE IF EXISTS grantRoleToUsersFromWhere;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE grantRoleToUsersFromWhere(IN grantRole VARCHAR(30), IN userTable VARCHAR(30), IN addCondition VARCHAR(50))

BEGIN
    DECLARE workUser VARCHAR(30) default '';    
    DECLARE gRole VARCHAR(30) default grantRole;
    DECLARE uTable VARCHAR(30) default userTable;
    DECLARE aCond VARCHAR(50) default addCondition;

    DECLARE cur1 CURSOR FOR SELECT Name FROM uTable WHERE aCond;

    OPEN cur1;    
    read_loop: LOOP
        FETCH cur1 INTO workUser;
        GRANT gRole TO workUser;
    END LOOP;    
    CLOSE cur1; 
END $$
DELIMITER ;

Answer 1

Create dynamic cursors directly is not possible. You can however use VIEW's to achieve the same thing. See sample.

CREATE PROCEDURE p1 (select_statement VARCHAR(255))
BEGIN
  DECLARE v1,v2 VARCHAR(255);
  DECLARE c CURSOR FOR SELECT * FROM t;
  SET @v = CONCAT('create temporary table t as ',select_statement);
  PREPARE stmt1 FROM @v;
  EXECUTE stmt1;
  OPEN c;
  FETCH c INTO v1,v2;
  SELECT v1,v2;
END//