Solved

Defining a MySQL Stored Procedure with a parameter

Posted on 2008-10-02
4
524 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

679 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