MySQL Stored Procedure that have Variable Table Name

First Time Using Stored Procedure in MySQL 5 is a high class type of database programming to me. I still newbie in this kind of programming.

But in this article i want to show you that a direct variable table name doesn't work in MySQL Stored Procedure. The example below will got error table name

DROP PROCEDURE IF EXISTS addnew;

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `db_simaset`.`addnew`(in ptabel varchar(20), in kodeaset varchar(20), in banyak numeric, in awalancounter numeric)

BEGIN
set @awalancounter=awalancounter;
set @inc=1;
set @ptabel = ptabel ;

while (@inc <= banyak) do begin set @awalancounter=@awalancounter+1; insert into @ptabel (kode_aset, no_aset) values(kodeaset,@awalancounter); set @inc=@inc+1; end;
end while;

END$$

DELIMITER ;

then, to make it works, it should be like this (Using Concat ( String Function))

DELIMITER $$

DROP PROCEDURE IF EXISTS `db_simaset`.`addnew`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `addnew`(in tabel varchar(15), in kodeaset varchar(11), in banyak numeric, in awalancounter numeric)

BEGIN
set @awalancounter=awalancounter;
set @inc=1;
set @ptabel=tabel;

while (@inc <= banyak) do
begin
set @awalancounter=@awalancounter+1;
SET @dyn_sql=CONCAT("INSERT INTO ",@ptabel,"(kode_aset, no_aset) values (",kodeaset,",",@awalancounter,");");
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
set @inc=@inc+1;

end;
end while;

END$$

DELIMITER ;

Related Post: