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.