Solved

Defining a MySQL Stored Procedure with a parameter

Posted on 2008-10-02
4
521 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
ID: 22630657
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
ID: 22631247
0
 
LVL 1

Author Comment

by:Simon336697
ID: 22631434
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
ID: 22631443
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

13 Experts available now in Live!

Get 1:1 Help Now