SQL SERVER – Stored Procedures Advantages and Best Advantage

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.

I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

Reference : Pinal Dave (http://blog.SQLAuthority.com)

http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/

Stored Procedures vs Parameterized Queries [closed]

From the .NET tag (and only from the tag) I'm going assume SQL Server here. Parametrized queries are excellent for projects that have a single application and modest security requirements. The require less effort to set up, and many ORM systems are going to use them if you like it or not.

On the other hand, if your database is handled by multiple applications and you need to enforce consistency of access, or if you need complex security (partial access to tables, either by column or by row or both) you should be using stored procedures. They allow you to enforce your security requirements and provide a consistent interface so applications don't violate your data rules.

As far as performance goes, I haven't noticed enough difference between the two to care these days: parametrized queries plan caching seems to work well enough.

http://stackoverflow.com/questions/230668/stored-procedures-vs-parameterized-queries