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..

Komentar FB


Probably Related Article



1 comments:

MySQL Code Samples said...
January 3, 2010 at 11:05 AM

Another great mySQL tutorial, thanks a bunch.

Related Posts by Categories



Widget by