I'm quoting part of an answer which I received for another question of mine:
In the PHP/MySQL world I would say stored procedures are no-go
I would like to know: Is that so? Why? Why not?
[edit]I mean this as a general question without a specific need in mind[/edit]
Do you have a specific need in mind which makes you consider them? Stored procedures are much less portable than "plain" SQL, that's usually why people don't want to use them. Also, having written a fair share of PL/SQL, I must say that the procedural way of writing code adds complexity and it's just not very modern or testable. They might be handy in some special cases where you need to optimize, but I'd certainly think twice. Jeff has similar opinions.
There's possibly a phobia of stored procedures with mysql, partly due to not being overwhelmingly powerful ( compared to Postgresql and even MSSQL, mysqls stored procedures are greatly lacking ).
On the plus: They make interfacing with it from more than one language easier.
If somebody states that "using stored procedures is bad because its not portable to different databases" then this of course means they think you're likely to switch databases, which means they in turn saying they think you shouldn't be using mysql.
It is popular to use ORM's these days, but I personally think ORM is a BadThing (Question:82882)
This is a subjective question.
I would personally include all calculations within PHP and only really use MySQL as a table.
But, If you feel that it is easier to use stored procedures then by all means, go ahead and do it.
In the context of this question I don't have a specif need in mind.
The statement seems very categorical to me and I would like to know the opinions of experienced programers about it. I would like to find out to what extent this statement is generally agreed upon and wheather or not there is a controversy attached to this topic.
I would not say "stored procedures are a no-go", I would say "Don't use them without a good reason".
MySQL stored procedures have a particularly horrible syntax (Oracle and MSSQL are pretty awful too), maintaining them just complicates your application.
Do use a stored procedure if you have a real (measurable) reason to do so, otherwise don't. That's my opinion anyway.
When using stored procedures with MySQL, you will often need to use the mysqli interface in PHP and not the regular mysql interface.
The reason for this is due to the fact that the stored procedures often will return more than 1 result set. If it does, the mysql API can not handle it and will you get errors.
The mysqli interface has functions to handling these multiple result sets, functions such as mysqli_more_results and mysqli_next_result.
Keep in mind that if you return any result set at all from the stored procedure, then you need to use these APIs, as the stored procedure generates 1 result set for the actual execution, and then 1 additional one for each result set intentionally returned from the stored procedure.
I develop and maintain a large PHP/MySQL application. Here is my experience with stored procedures.
Over time our application has grown very complex. And with all the logic on the php side, some operations would query the database with over 100 short queries.
MySQL is so quick that the performance was still acceptable, but not great.
We made the decision in our latest version of the software to move some of the logic to stored procedures for complex operations.
We did achieve a significant performance gain due to the fact that we did not have to send data back and forth between PHP and MySQL.
I do agree with the other posters here that PL/SQL is not a modern language and is difficult to debug.
Bottom Line: Stored Procedures are a great tool for certain situations. But I would not recommend using them unless you have a good reason. For simple applications, stored procedures are not worth the hassle.
I think that using stored procedures can offer some abstraction in certain applications, as in any where you would use the same SQL code chunk to update or add the same data, you could then create the one sproc save_user($attr.....) rather that repeating yourself all over the place.
Agreed the syntax is hairy and if your used to MSSQL and oracle sprocs there are differences that can fustrate.
You should also be aware that stored procedures were not supported in Mysql before version 5.0. http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html Also stored procedures tended to be be a bit weird in that implementation. Now that Mysql 5.1 is starting to crop up in the wild I see more use of stored procedures with Mysql.
I make limited use of stored procedures, and it works well. I am the lead dev for one of my companies clients, working on their e-comm website. The client has a stock system, we implemented a set of stored procedures on their system and built an API to communicate with it. This allowed us to abstract their database and they could implement logic in the stored procedures. Simple but met the business requirement very well.
I generally stay away from stored procedures because it adds load to the database which is 99% of the time, your biggest bottleneck. Adding a new php server is nothing compared to making your MySQL db replicate.