how to pass dynamic table name into mySQL Procedure with this query?

Question

I have create procedure and when i pass table name manually then its working fine, but when i pass dynamic table name then it says dbname.tblname doesn't exist.

DELIMITER $$
CREATE
PROCEDURE `lmsonline`.`delProc`(tblName VARCHAR(20),sr INT)    
BEGIN
DELETE FROM tblName WHERE srno=sr; 
SET @num := 0;
UPDATE tblName SET srno = @num := (@num+1);
ALTER TABLE tblName AUTO_INCREMENT = 1;
END$$
DELIMITER ;

and to execute i have CALL delProc('beginner',6);

have you tried [prepared statement](http://stackoverflow.com/questions/13705045/dynamic-table-names-in-stored-procedure-function)? — Bagus Tesa, Dec 29 '15 at 06:45
i have no idea to execute multiple queries in prepared statement as well as stored procedure. @Tezla — KuldeeP ChoudharY, Dec 29 '15 at 06:49

Bernd Buffen · Accepted Answer · 2016-01-02T10:42:06.310

1

A way to do it is to use prepared Statements like this.

DELIMITER $$
CREATE
PROCEDURE `delProc`(tblName VARCHAR(20),sr INT)    
BEGIN
  SET @SQL := CONCAT('DELETE FROM ', tblName,' WHERE srno=',sr);
  PREPARE stmt FROM @SQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;      

  SET @num := 0;
  SET @SQL := CONCAT('UPDATE ', tblName,' SET srno = @num := (@num+1)');
  PREPARE stmt FROM @SQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;      

  SET @SQL := CONCAT('ALTER TABLE ', tblName,' AUTO_INCREMENT = 1');
  PREPARE stmt FROM @SQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;      
END$$
DELIMITER ;

edited Jan 02 '16 at 10:42

answered Dec 29 '15 at 07:16

Bernd Buffen

14,525
2
24
39

What should i do for this two query ? SET num := 0; UPDATE tblName SET srno = @num := (@num+1); ALTER TABLE tblName AUTO_INCREMENT = 1; – KuldeeP ChoudharY Dec 29 '15 at 09:23
I have change my answer. but i dont know why you @num ? you set it always to 0 and then you add 1. so its 1 – Bernd Buffen Dec 29 '15 at 09:42
By using this query i delete a row from table but i have to keep the sequence as it is. number of rows can be decrease but the sequence will not change like if you delete 2 no row from table which contains 1 2 3 4 then the table keep 1 3 & 4 but i want 1 2 3 that's it [ Reference] (http://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql) – KuldeeP ChoudharY Dec 29 '15 at 09:52

score 0 · Answer 2 · answered Dec 29 '15 at 06:49

0

Try using [] around your table Name , sometime it may happen that it would be a system table having same name as your table name. e.x. [beginner] .

Thanks.

answered Dec 29 '15 at 06:49

Utsav

16
3

Not working it says You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[beginner],6)' at line 1 – KuldeeP ChoudharY Dec 29 '15 at 06:51

score 0 · Answer 3 · answered Dec 29 '15 at 07:26

    CREATE PROCEDURE `lmsonline`.`delProc`(IN tblName VARCHAR(20),sr INT)    
    BEGIN
      SET @SQL := CONCAT('DELETE FROM ', tblName,' WHERE srno=',sr);  
   PREPARE stmt3 FROM @t1;
     EXECUTE stmt3;
     DEALLOCATE PREPARE stmt3;
    END$$
    DELIMITER ;
    ...try this way ,if not working check whether table exists or deleted by mistake or else.

    Thanks

how to pass dynamic table name into mySQL Procedure with this query?

3 Answers3