Stored Procedures

I'm working on a new project with a friend of mine and we started to talk about our database. I've never taken a formal class and it was interesting to design a database with him as he's taken a few. We got onto the subject of stored procedures. His professors have drilled it into his head to use them when ever possible. He's still open minded to them and I've personally never used them.

I started to do some reading and it seems that theres a lot of contravery to them.

Anyone else worked with stored procedures and whats your personal opinon on when to use them compared to dynamic SQL written in the application?

One advantage I see with store procedures is if your client application isnt always going to be up-to-date. Change your table struchure could break an application, so using store proecdures you can change your tables and still give them same data to the client.

Since this project is web based and updates to the "client" is complete control through us and no updates have to be installed I don't see much of a reason to use them. I don't have to worry about a client application breaking if I change the struchure of the database, because I can simply update the application.

DO NOT USE THEM. They are hell. A database administrator or one that has limited knowledge in things other than just databases as opposed to scripting or whole web networks may advocate them, but they are horrid.

I logged in just to post my opinion on this because of how much stored procedures have made my life a living hell at a company I work for that does affiliate marketing. For a few years they had a db admin and another programmer with limited knowledge create a gigantic sproc labyrinth. When I was working for the company as a full time employee, 50% of my job consisted of fixing problems that were caused by sprocs, whether it be permission errors, bugging sprocs, etc... you get limited output from them and they are hard as hell to trace back as well among many other things. These days the disadvantages of using theme severely outweigh any benefits. Here's some useful information I've collected over a short period of time.

Problems with Stored Procedures
- http://www.tonymarston.net/php-mysql/st ... -evil.html
- Stored Procedures cannot easily be run over SVN (version control)
- Most modern databases compile and cache dynamic MySQL the same as stored procedures.
- Cannot use IN() statements & if you pass data as a string it's more vulnerable to MySQL injection.
- Not using IN() doesn't allow for much flexibility of using non-nested query loops.
- Modifications of SQL requires an entirely different application, modifying seperate (Takes a lot of time).
- Cannot do massive searches on SPROCS
- Extremely difficult to dynamically modify query to do things like loop like statements & explode/pass array values.
- Poor database support in MySQLi - Returns extra rows, special settings have to be setup.
- Difficult to cache queries locally because they may return different values.
- Since they are defined once and used many times, if changes are made to them, they must be replaced and affect ALL pieces of the software.
- Since they are defined once and used many times, they usually call more information than they need to.
- Changes in one play cause problems in others
- If you feed a sproc something, a lot of times you end up calling it for no reason if the value is NULL
- A lot of times, changes need to be made to the application when a sproc is changed and usually it involves an inefficient solution.
- It's slightly harder to secure sprocs, as it's difficult to manage security filters on the application side for constantly changing sprocs and when there is a hole in the sproc, the vulnerability is worse.

In my opinion I'm not trying to troll, and I'm probably not going to reply back to this thread; I'm just trying to help you out man.