how to user table name as variable in mysql stored procedure

Question

I create a procedure to set auto-increment index value to 1. But there I want to use table name as a variable. I am try it but it not working correctly. I know there have a lot of question like that but those are not help me to solve my problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
 SELECT table_name FROM information_schema.tables where table_schema='maliban';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop: 
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
   ALTER TABLE var1 AUTO_INCREMENT = 1;
  END IF;
 END LOOP;
END

This is the my stored procedure. I want to user var1 as a variable in bellow line

ALTER TABLE var1 AUTO_INCREMENT = 1;

I already use it as a variable but it not working. because of it get like a table name.

please help me. I am new to the stored procedure.

Answer 1

ALTER TABLE var1 AUTO_INCREMENT = 1;

we can replace above line in below code fragment. Then it will solve this problem.

SET @SQL = CONCAT('ALTER TABLE `',var1,'` AUTO_INCREMENT = 1');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Complete code :

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
 SELECT table_name FROM information_schema.tables where table_schema='maliban';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop: 
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
   SET @SQL = CONCAT('ALTER TABLE `',var1,'` AUTO_INCREMENT = 1');
   PREPARE stmt FROM @SQL;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END IF;
 END LOOP;
END