In general, Stored Procedures provides the following benefits over direct SQL in your application

• Precomplied Execution : Database server complies each stored procedure once and then re-uses the execution plan that results in tremendous performance boosts when a stored procedure called repeatedly.


• Data Abstraction : An stored procedure can be used by multiple users and applications that facilitates a common data services for the application(s) resulting in reduced development life cycle.


• Ehanced Security Controls : One can develop access controls for a stored procedure independent of underlying tables.


• Reduced Client Server Traffic : Stored procedures optimizes the data transfer traffic between database server and application server.

On other hand, stored procedure demands more resources at the database server; and an effective and efficient implementation of store procedures also demands lots of planning and partnership among data, system and business architects.

Disclaimer

The thoughts expressed in the blog are those of the author and do not represent necessarily the official policy or position of any other agency, organization, employer, or company. Assumptions made in the study are not reflective of the point of view(s) of any entity other than the author. Since we are critically thinking human beings, the point of view(s) is always subject to change, revision and rethinking at any time. While reasonable efforts have been made to obtain accurate information, the author makes no warranty, expressed or implied as to its accuracy.