From my position with my organization, I work with MySQL databases on a regular basis.
I have to be honest with myself and say I do not have a working knowledge of SQL. In that, I understand the differences in the procedures each uses; with differences and similarities in functions, form and data processing. SQL stored procedures allow database programmers to avoid errors in creating queries for operations.
According to Coronel & Robb in Database Systems Design, Implementation, and Management, the primary advantage gained from using stored procedures is encapsulation. Encapsulation streamlines the execution of a set of SQL statements improving performance and reducing the duplication of useful code unnecessarily (2005).
Another benefit is the fact that stored procedures have been tested and debugged by database developers, improve the reliability of the procedures (Mandayam, 2005). In terms of Enterprise databases, stored procedures can be useful depending on whom one speaks to. According to a number of database administrators on Stackoverflow, in the case of Enterprise databases encapsulated queries lack the value add needed to be useful in large scale operations.
Stored procedures are good at handling the simple CRUD operations (Create, Read, Update and Delete) but not Enterprise level queries (Karras, 2009). As an example of the converse, IBM’s DB2 Everyplace, which is designed for large scale databases uses pre-programmed procedures to for managing file subscriptions and application distribution (IBM, N.D.). Evaluating the needs of organization looking to implement a database will help determine the benefits stored procedures can offer.
IBM. (N.D.). DB2 Everyplace: Features and benefits. IBM Information Management. Retrieved from, http://www-01.ibm.com/software/data/db2/everyplace/about.html
Karras, C. (2009). Disadvantage of stored procedures. Stack Overflow. Online resource. Retrieved from, http://stackoverflow.com/questions/226859/disadvantage-of-stored-procedures
Mandayam, P. (2005). Why use stored procedures? SearchSQLServer.com. Online resource. Retrieved from, http://searchsqlserver.techtarget.com/news/1052737/Why-use-stored-procedures
Robb, P., & Coronel, C. M. (2005). Database systems design, implementation, and management. Boston, MA: Course Technology.
About the Author: Chris Turner is also known as ChocolateSEO. CSEO is Chris' Nashville search marketing and consulting service offering a variety of services to help you, your company and any website maximize web-based marketing opportunities. He is the father of three girls, one boy (finally) and husband to the wonderful Savannah. Join the author's circle: Chris Turner on Google+.
Follow Us! Sharing Is Caring!