Solved

Defining a MySQL Stored Procedure with a parameter

Posted on 2008-10-02
4
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 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