Solved

Defining a MySQL Stored Procedure with a parameter

Posted on 2008-10-02
4
520 Views
Last Modified: 2012-06-27
Hi everyone.
I have the following MySQL stored procedure which works fine.
The only thing is, is that it only allows me to insert one record at a time.
What I would like to do is to modify this so that I can use this sproc, but insert as many records as required.
Any help greatly appreciated.

Usage is:
call paraddproject ('MySQL Server Upgrade Project');

What I would like is the following:

call paraddproject (
'MySQL Server Upgrade Project',
'Another Project',
'Another Project2',
'Another Project3'
);

Thanks guys.
Usage: call paraddproject ('SQL Server Upgrade Project');

*************************************************************************/

DELIMITER // CREATE PROCEDURE paraddproject(

IN projectname varchar(255)

)

BEGIN

INSERT INTO projects_pro (name_pro) values

(projectname);

END;

//

DELIMITER ;

Open in new window

0
Comment
Question by:Simon336697
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Goodangel Matope earned 350 total points
Comment Utility
Stored procedures accept a set number of parameters. Are you using PHP on this, because PHP has an explode() function you can use, and just pass the entire list of parameters as one comma separated string.

You can also write a stored procedure that accepts one long string which is comma separated, but in MySql this might become an exciting little venture with lots of substring and charindex calls!
0
 

Assisted Solution

by:klay8
klay8 earned 150 total points
Comment Utility
0
 
LVL 1

Author Comment

by:Simon336697
Comment Utility
Hi Goodangel and klay8....really appreciate your kind input guys :>)

Goodange....i use php, but not sure how i would implement using the explode with a stored procedure.

Maybe something like...

$array = ('a,b,c,d,e');
call spmy_storedprocedure ('explode(',',$array)')

Is this the way you meant goodange?
0
 
LVL 1

Author Comment

by:Simon336697
Comment Utility
Hi klay, thank you for the link....but that seems to target SQL Server and not MySQL, which has totally different syntax...much appreciated anyway mate :>)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now