CODEWITHSUNDEEP
mysqlstored-procedures
KuldeeP ChoudharY
KuldeeP ChoudharY

Reputation: 428

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

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);

Upvotes: 1

Views: 2805

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

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 ;

Upvotes: 1

Utsav
Utsav

Reputation: 16

    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

Upvotes: 0

Utsav
Utsav

Reputation: 16

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.

Upvotes: 0

Related Questions