MySQL Stored Procedure that have Variable Table Name and it's Query Validation

I have try and try about MySQL Stored Procedure that have Variable Table Name and it's Query Validation. First i try to make MySQL Stored Procedure that have Variable Table Name. I so newbie in stored procedure, but after any success it feel so nice.

This stored procedure primary target is giving a increment counter in a field. The increment value of a asset (Asset Information System) giving a unique ID between each number of asset even the asset is the same.

below is primary procedure to do the increment.

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,
 codesppa varchar(20),
 id_transaksi numeric,
 tglpenerimaan date,
 tglpembukuan date)
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,
no_sppa,id_transaksi,tgl_penerimaan,tgl_pembukuan)         
values ('", kodeaset,"','", @awalancounter,"','",codesppa,"','",     
id_transaksi,"','",tglpenerimaan,"','",tglpembukuan,"');");       

PREPARE s1 FROM @dyn_sql;    
EXECUTE s1;   set @inc=@inc+1;         
end;  
end while;       
END$$  
DELIMITER ; 

the validation is give a single quoted beetwen dynamic value of the field. I mean Here

'",kodeaset,"'

without the validation it will be error column will happen shows that MySQL confuse about integer and varchar value.

Hope it will help you..

Related Post: