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 ;

Komentar FB


Probably Related Article



48 comments:

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

Thanks, nice and clear tutorial.

Excel Tips and Tutorials said...
June 10, 2010 at 12:07 AM

I had very bad experience with mysql stored procedure and triggers. I think mysql should provide a tool for stored procedure programming with debug facility.

stag weekends said...
July 15, 2010 at 3:51 AM

I want to return Tasks by ID if a Task ID is supplied, if not supplied I want to return all Tasks. How do I accomplish this using a SQL stored procedure?

fun team building said...
August 4, 2010 at 11:57 PM

What may be the reason behind an oracle stored procedure running slow in a particular schema?

Negligence Injury Claims said...
August 12, 2010 at 10:02 PM

Nothing works. I can't get MySQL to work on my mac OS 10.5.5.Is there some trick to installing it and getting to
show up.

Cheap Web Design said...
August 13, 2010 at 1:43 AM

I use views for de-normalisation or output formatting and stored procedures for filtering and data manipulation (things that require parameter inputs) or iteration (cursors).

Event Management Service said...
August 14, 2010 at 4:45 AM

I have a database in MySql and I want to keep the same webpage layout for each page, so I want use the database to replace the "placeholders" in php. How do you do this? Can somebody provide a walkthrough?

poster printing said...
September 2, 2010 at 9:27 PM

I use views for de-normalization or output formatting and stored procedures for filtering and data manipulation...

Manchester Hen Party said...
November 30, 2010 at 8:11 PM

I need to extract data from 2 MySQL databases located at two different servers. How is it possible from one single PHP page?

Blackpool Stag said...
December 1, 2010 at 3:42 AM

What may be the reason behind an oracle stored procedure running slow in a particular schema?

four poster beds said...
December 9, 2010 at 1:20 AM

I am using the insert command to enter data.It keeps inserting data in random locations in the table, which is messing up my web app.It did use an id column at one time.

Amsterdam Weekend said...
December 22, 2010 at 12:13 AM

I think mysql should provide a tool for stored procedure programming with debug facility.

Behavior interview said...
May 10, 2011 at 5:30 AM

These different variants methodologies that need to be understand able at last to increase the visibility after all.

facebook marketing said...
May 10, 2011 at 11:56 PM

Very useful tutorial on MySql stored procedures.Hope you can share something triggers also.

free credit report said...
May 11, 2011 at 12:15 AM

Stored Procedures and Triggers are a tricky subject in MySql. Its not easy to find such good resources like this one that you have shared.

marketing on facebook said...
May 11, 2011 at 1:17 AM

It?s the old what goes around comes around routine. Did you acquired lots of links and I see lots of track backs??

Double glazing said...
May 11, 2011 at 2:04 AM

This is a good collection of detailed info on stored procedures. Thanks for sharing this.

black mamba said...
May 11, 2011 at 11:55 PM

I am also a newbie to PL/SQL programming, and still updating on the basics. This article of yours is quite useful.

Double glazing said...
May 12, 2011 at 5:10 AM

The gift to laughter is to think positively and be positive about the fact and frictions which acting in your day to day life.

how to get rid of a yeast infection naturally said...
May 17, 2011 at 4:55 AM

Good article on stored procedures, wish you can share some on triggers as well.

surgicel said...
May 18, 2011 at 1:00 AM

mysql should provide a tool for stored procedure programming with debug facility.

What career said...
May 18, 2011 at 1:05 AM

MySql ain't working on my Macbook, can anyone give me a heads-up ?

villa rental in Cannes said...
May 19, 2011 at 6:11 AM

It is true the dreams are the end product of what we gather in our conscious mind and then as according to our liking and preference we manipulate it.

personality testing said...
May 26, 2011 at 5:24 AM

I appreciate your effort in sharing this good tutorial on sql stored procedures.

gamer said...
May 31, 2011 at 12:43 AM

I would like you to provide some more walkthroughs on MySql triggers.

organic seo company said...
June 1, 2011 at 10:37 PM

I also think Mysql should provide a stored procedure programming tool.

hedging said...
June 7, 2011 at 4:25 AM

Your article provides a good grasp on the technical aspects.

play to win prizes said...
June 15, 2011 at 12:39 AM

Very informative tutorial on this topic.

Outsourcing in India said...
June 17, 2011 at 6:08 AM

I had very bad experience with mysql stored procedure and triggers.

villa for film said...
June 17, 2011 at 6:11 AM

I can't get MySQL to work on my mac OS 10.5.5.Is there some trick to installing it ?

Cannes apartment rental said...
June 17, 2011 at 6:12 AM

This article of yours is quite useful.

Cannes property said...
June 17, 2011 at 6:14 AM

Wish you can share some info on triggers.

free from depression said...
July 12, 2011 at 1:06 AM

It is really a good post and telling all necessary about MySQL.thanks for represent it.

Ladder said...
July 20, 2011 at 3:27 AM

Its a very good post submit.nice to see it.please keep it up.

villa for film said...
July 20, 2011 at 6:05 AM

Really very appreciate work.

Curved stairlifts said...
August 26, 2011 at 2:33 AM

This is a very well written post and theme is also very nice of this post.I like it very much.Keep it up.

Android developer said...
September 12, 2011 at 2:19 AM

Your blog is looking good and i likes your blog background color, i think it can easily to attract the visitors and to increase your blog traffics.

Domain registration said...
November 25, 2011 at 9:24 PM

Great work You mention this topic very well. I really enjoy reading your blog and I will definitely bookmark it! Keep up the interesting posts!

Hedging Plants said...
April 10, 2012 at 5:39 AM

i am very thankful to you provide latest information about. i like this kind of informative information about it.

Free Corporate Training in Noida said...
April 10, 2012 at 11:30 PM

Have you great work again .i am thankful to you did brillent work again. i have bookmarked you webpage.

top engineering colleges in haryana said...
April 11, 2012 at 2:46 AM

I am glad that being part of your webpage. great work by you. I am very thankful .

yourcarparts.co.uk reviews said...
July 23, 2012 at 2:47 AM

I had very bad encounter with mysql database saved process and activates. I think mysql database should provide a device for saved process development with debug service.

homes for sale Keller Texas said...
July 28, 2012 at 2:50 AM

wow what a post ! i am really impressed and appreciate. i like this kind of blog. thanks for shain

gifts to pakistan said...
July 28, 2012 at 3:24 AM

wow amazing blog @ It's actually a awesome and useful item of details. I am satisfied that you just assigned this details with us. Please remain us suggested like this. Thank you for offering.

aluminium scaffolding said...
July 31, 2012 at 12:00 AM

Really awesome blog ! i am very happy to see it. i like this kind of blog . every one like your blog. thanks for sharing.

Gardnerville NV Real Estate said...
July 31, 2012 at 4:40 AM

i am very fulfilled to see your web page. thnaks for shairng. . this is very valuable and useful post. i like this kind of blog page. thanks for providing. i am happy who provide latest information appropriate this blog page.

wood flooring said...
August 4, 2012 at 1:08 AM

i had very bad experience with mysql data source database stored procedure and triggers. I think mysql data source database should provide a device for stored procedure growth with debug service.

GITM singh said...
March 8, 2013 at 10:13 PM

Hi,
very good blog and the information you are providing is too useful i have some additional information about top colleges if want know then plz visit Top Engineering College In india

Related Posts by Categories



Widget by