0

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.

2
  • 1
    yes, prepared statements like you have done are the only way to perform dynamic queries. Generally resetting AUTO_INCREMENT is a strange thing to do and can lead to troubles. Using a decent data type you'll never need to reset it. Treat AI values as an arbitrary designator and you'll never need to do this. – danblack Oct 27 '18 at 21:39
  • I am not resetting AUTO_INCREMENT value in existing system and I know how much trouble I can get because of that. I am using workbench and when i get database model there AUTO_INCREMENT value not reset to default that's why I am doing this. thanks for the advice. – Supun Kavinda Oct 28 '18 at 3:30
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
1
  • Sorry to necro-comment, but how safe is this CONCAT statement? – Mike Warren Nov 7 '20 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.