CODEWITHSUNDEEP
mysqlstored-procedures
Alan
Alan

Reputation: 2086

Can't specify table name on mysql stored procedure

I am mass assigning new id numbers to things in the DB to make room for some stuff at the beginning of each table. I created a procedure that works, but when I try adding input parameters to allow scripting, it can't find the table

delimiter |
CREATE PROCEDURE changeID 
( IN in_table_name varchar(64)) 
BEGIN 
  DECLARE done INT DEFAULT FALSE;
  DECLARE a,b INT DEFAULT 800000;
  DECLARE cur1 CURSOR FOR SELECT id  FROM in_table_name ORDER BY id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
  FETCH cur1 INTO b;
    IF done THEN
      LEAVE read_loop;
    END IF;
        UPDATE in_table_name SET id = a + 1 where id = b;
        SET a = a+1;

END LOOP;
  CLOSE cur1;
END; 
|
delimiter ;

When I run this using call changeID('users'), I get the error:

[Err] 1146 - Table 'databaseName.in_table_name' doesn't exist

I was hoping to loop through using a simple list of commands like this so it could run unattended instead of manually changing the in_table_name between each execution:

call changeID('users');
call changeID('appointments');
call changeID('groups');

Upvotes: 0

Views: 851

Answers (2)

Alan
Alan

Reputation: 2086

KChason got me started in the right direction, but I had to take it a little further to get the first part working from tips here: https://forums.mysql.com/read.php?98,138495,138908#msg-138908.

DROP PROCEDURE
IF EXISTS `workingversion`;

delimiter |

CREATE PROCEDURE `workingversion` (IN tableName VARCHAR(100))
BEGIN

DECLARE done INT DEFAULT 0 ;
DECLARE a,
 b INT DEFAULT 800000 ;
DROP VIEW IF EXISTS v1; 
SET @stmt_text = CONCAT("CREATE VIEW v1 AS SELECT id FROM ",    tableName,  " ORDER BY id") ; 
PREPARE stmt
FROM
    @stmt_text ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ;
BEGIN

DECLARE cur1 CURSOR FOR SELECT * FROM v1 ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1 ; OPEN cur1 ;
REPEAT
    FETCH cur1 INTO b ;
IF NOT done THEN
SET @Query = CONCAT('UPDATE ',tableName,' SET `id` = ',a+1,' WHERE `id`=',b);
PREPARE stmt FROM @Query;
EXECUTE stmt;

        SET a = a+1;
END
IF ; UNTIL done
END
REPEAT
    ; CLOSE cur1 ;
END ;
END

Upvotes: 0

kchason
kchason

Reputation: 2885

You can't dynamically pass a table name in a query, however, you can concatenate a string and then execute it as a statement. You of course want to be careful and ensure that this data has been sanitized etc. I wasn't able to test this, but something to this effect should get you going.

...
END IF;

SET @Query = CONCAT('UPDATE ',in_table_name,' SET `id` = ',a+1,' WHERE `id`=',b);
PREPARE stmt FROM @Query;
EXECUTE stmt;
...

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Upvotes: 1

Related Questions