The topic of database stored procedures does not cease to divide the community of database application developers and DBAs. There are many articles around, expressing various opinions about this subject. But what do you think?
Please let me know and take this survey. The survey contains 5 multiple choice questions, and will take about 1 to 5 minutes to complete.
The results will be posted soon here on my blog at http://rpbouman.blogspot.com/, and also on the O’Reilly database blog. Thank you in advance!
(Disclaimer: I’m an employee of MySQL AB, but this survey does not in any way relate to my position with my employer. Also, I have made an utmost effort to write an unbiased survey. If you feel that something is wrong in this regard, please post a comment on this post.)
Who has been suggesting that sprocs are in any way bad?
They are compiled server-side, so they are going to be generally faster.
The "relational" remarks are irrelevant. The point of SOAP is to abstract away the details of XML data exchange. Similarly, sprocs abstract away the details of data storage, at no speed penalty. There is no conflict here.
Like the typical programming best practice of providing public methods or properties rather than fields for objects, sprocs allow you to provide better security by providing extremely granular security, allowing users access only to, for example, a *specific implementation* of an UPDATE statement, rather than direct table access.
Finally, sprocs protect you from SQL injection attacks better than SQL strings do (provided you use command objects rather than passing SQL strings to the database, which should always be grounds for termination).
I cannot think of any reason why anyone would use SQL strings in production code. Period.
Stored procedures is bad,don't use it.
It's a pity that if you want to specify some "other" opinion, the input box's limit of characters is too small to write anything meaningful
In my exeperience the only people who don't like stored procedures are not "database application developers" but some middle-tier or front end developers who are basically antagonistic to databases as such.
Cheers, APC
All depends upon the application... ...but the one question I did answer definitively (besides the language/DB experience pair) is on how it makes the application less portable. I realize some would argue to the contrary, but embedding stored procedures into an application kind of ties you to a particular database and to me, the ability to plug in a different SQL database engine seems to be portability on a level far greater than carrying stored procedures in a given dialect over...
hi.
Bob, what you think about this?