Solved

Defining a MySQL Stored Procedure with a parameter

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 51
PHP connection to remote AWS MySQL RDS 4 95
MySQL: Updating SubQuery Match Faster 9 50
How to import sql database into mysql workbench 18 34
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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