MySQL stored procedures use them or not to use them

Unlike actual programming language code, they:

• not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible - I've seen it!)
• not easily testable - you need a real (dev) database instance to test them and thus unit testing their code as part of a build is virtually impossible
• not easily updatable/releasable - you must drop/create them, ie modify the production db to release them
• do not have library support (why write code when someone else has)
• are not easily integratable with other technologies (try calling a web service from them)
• they use a language about as primitive as Fortran and thus are inelegant and laborious to get useful coding done, so it is difficult to express business logic, even though typically that is what their primary purpose is
• do not offer debugging/tracing/message-logging etc (some dbs may support this - I haven't seen it though)
• lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
• people skilled in coding them are rarer and more expensive than app coders
• their "high performance" is a myth, because they execute on the database server they usually increase the db server load, so using them will usually reduce your maximum transaction throughput
• inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure - very inefficient)
• etc.

If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.

Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.

Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.

Unlike programming code, they:

• render SQL injection attacks almostimpossible (unless you are are
  constructing and executing dynamic
  SQL from within your procedures)
• require far less data to be sent overthe IPC as part of the callout
• enable the database to far bettercache plans and result sets (this isadmittedly not so effective withMySQL due to its internal cachingstructures)
• are easily testable in isolation(i.e. not as part of JUnit tests)
• are portable in the sense that theyallow you to use db-specificfeatures, abstracted away behind aprocedure name (in code you are stuckwith generic SQL-type stuff)
• are almost never slower than SQLcalled from code

but, as Bohemian says, there are plenty of cons as well (this is just by way of offering another perspectve). You'll have to perhaps benchmark before you decide what's best for you.

As for performances, they have the potential to be really performant in a future MySQL version (under SQL Server or Oracle, they are a real treat!). Yet, for all the rest... They totally blow up competition. I'll summarize:

• Security: You can give your app the EXECUTE right only, everything is fine. Your SP will insert update select ..., with no possible leak of any sort. It means global control over your model, and an enforced data security.

• Security 2: I know it's rare, but sometimes php code leaks out from the server (i.e. becomes visible to public). If it includes your queries, possible attackers know your model. This is pretty odd but I wanted to signal it anyway

• Task force: yes, creating efficient SQL SPs requires some specific resources, sometimes more expensive. But if you think you don't need these resources just because you're integrating your queries in your client... you're going to have serious problems. I'd mention the analogy of web development: it's good to separate the view from the rest because your designer can work on their own technology while the programmers can focus on programming the business layer.

• Encapsulating business layer: using stored procedures totally isolates the business where it belongs: the damn database.

• Quickly testable: one command line under your shell and your code is tested.

• Independence from the client technology: if tomorrow you'd like to switch from php to something else, no problem. Ok, just storing these SQL in a separate file would do the trick too, that's right. Also, good point in the comments about if you decide to switch sql engines, you'd have a lot of work to do. You have to have a good reason to do that anyway, because for big projects and big companies, that rarely happens (due to the cost and HR management mostly)

• Enforcing agile 3+-tier developments: if your database is not on the same server than your client code, you may have different servers but only one for the database. In that case, you don't have to upgrade any of your php servers when you need to change the SQL related code.

Ok, I think that's the most important thing I had to say on the subject. I developed in both spirits (SP vs client) and I really, really love the SP style one. I just wished Mysql had a real IDE for them because right now it's kind of a pain in the ass limited.