Use variable when declaring cursor

Question

I want to pass the parameter to the procedure and use it for the table name on declaring cursor. The following code returns an error message: #1146 - Table 'db.table_id' doesn't exist.

How do I use the parameter when declaring cursor?

Thanks

delimiter ;;

drop procedure if exists reset_id;;

create procedure reset_id(table_id VARCHAR(25))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE id_new INT;
    DECLARE getid CURSOR FOR SELECT entryId FROM table_id ORDER BY entryId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @id_new = 1; 

    OPEN getid;

    FETCH getid into id;
    REPEAT
        UPDATE table_id SET entryId = @id_new WHERE entryId = id;
        SET @id_new = @id_new + 1;
        FETCH getid into id;
    UNTIL done END REPEAT;
    CLOSE getid;
END
;;

CALL reset_id('Test');

After modifying the procedure, still returns an error #1324 - Undefined CURSOR: getid. How do i solve this problem?

delimiter ;;

drop procedure if exists test2;;

create procedure test2(table_id VARCHAR(25))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE id_new INT;
    DECLARE stmt1 VARCHAR(1024);
    DECLARE stmt2 VARCHAR(1024);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @sqltext1 := CONCAT('DECLARE getid CURSOR FOR SELECT entryId FROM ',table_id,' ORDER BY entryId');
    PREPARE stmt1 FROM @sqltext1;
    EXECUTE stmt1;

    SET @id_new = 1; 

    OPEN getid;

    FETCH getid into id;
    REPEAT
        SET @sqltext2 := CONCAT('UPDATE ',table_id,' SET entryId = ? WHERE entryId = ?');
        PREPARE stmt2 FROM @sqltext2;
        EXECUTE stmt2 USING @new_id, id;
        SET @id_new = @id_new + 1;
        FETCH getid into id;
    UNTIL done END REPEAT;
    CLOSE getid;
END
;;

CALL test2('Test');

If you want to have variable table names, you need to use prepared statements (also called dynamic SQL). — Gordon Linoff, Feb 08 '14 at 20:04
You need to create some statements as strings inside procedure. A very similar question and answer; http://stackoverflow.com/questions/3646412/mysql-query-using-variable-as-table-name-in-left-join — Serif Emek, Feb 08 '14 at 20:27

spencer7593 · Accepted Answer · 2014-02-11T20:27:13.910

The table name has to be specified in the SQL text; it cannot be a variable.

To accomplish what you are trying to do, you are going to need to dynamically create a string that contains the SQL text you want to execute.

To prepare the statement from arbitrary string:

SET @sqltext := CONCAT('UPDATE ',table_id,' SET entryId = ? WHERE entryId = ?');
PREPARE stmt FROM @sqltext;

Note that the table_id value is getting incorporated into a string variable, and then the PREPARE statement is (essentially) turning that string into an actual SQL statement.

To execute the prepared statement and supply values for the bind variables, you'd do something like this:

EXECUTE stmt USING @new_id, @id;

You can re-execute a prepared statement multiple times, without needing to prepare it again. So, the PREPARE would be done before your loop, the EXECUTE can be done inside the loop.

Once you are finished with the statement, following the loop, the best practice is to deallocate the statement like this:

DEALLOCATE PREPARE stmt;

NOTE:

The restriction about the table name not being a variable actually applies to all identifiers in a SQL statement, including names of tables, views, columns, functions, etc. Those all have to be literals in the SQL text, just like the reserved keywords do.

Thanks for the answer. I still have problems. How to use this method combined with CURSOR? I modified the procedure with your method but still returns an error. — user3274165, Feb 15 '14 at 15:12

Use variable when declaring cursor

1 Answers1