Need to get a listing of pros and cons for using Saved Methods. The primary benefit of SPs appears to become precompiled as well as an abstraction of information in the application. Produce your ideas....

Advantages: Supplies a "public interface" to some database (another abstraction layer).

Also groups all queries at same position, which makes it simpler for DBAs to determine the way the database is queried and optimize it accordingly.

Disadvantages: Might not be where to place complex logic. However, following the concept that complex logic goes in application code and never in saved methods, saved procedure become simply CRUD procedures (each table includes a "Create", "Read", "Update" and "Remove" procedure). For the reason that situation, saved methods don't add any value towards the application, they merely complexify maintenance and be waste.

Queries are arranged together, therefore it is harder to determine the context from the application where you have used them. Examining the impact of the change is longer, and doing the modification is longer too.

Therefore: use saved methods to encapsulate complex queries (complex joins, complex where clauses, ...). Try not to use saved technique of complex application/domain/business logic, and do not use saved methods for CRUD either. So saved methods ought to be utilized in a minority of cases instead of function as the standard tool for those queries within an application.

Group code (including queries) to attain "functional cohesion" rather than grouping by tool/technology. To permit a DBA to optimize a database depending on how it's being queried, make use of a profiler.

Correction: Whether they are precompiled is dependent around the database. In SQL Server, for example, they are not. Saved methods and parameterized SQL are generally put together prior to being run. A saved process could sometimes reuse an execution plan if your corresponding one is available...but so can parameterized SQL.

Edit: Here's what MSDN says about it:

SQL Server 2000 and SQL Server version 7. incorporate numerous changes to statement processing that extend most of the performance advantages of saved methods to any or all SQL claims. SQL Server 2000 and SQL Server 7. don't save a partly put together arrange for saved methods when they're produced. A saved procedure is put together at execution time, like every other Transact-SQL statement. SQL Server 2000 and SQL Server 7. retain execution plans for those SQL claims within the procedure cache, not only saved procedure execution plans.

Using the current .Internet 3.5 framework libraries, I'd use Linq to do most database procedures. There can be places where SP will work better. But Linq has provisions to operate an SP too.

Around the subject of disadvantages of SP, browse the link that follows - a fascinating analysis. Look into the blog post's comments too.

By utilizing SPs, additionally you avoid needing to give customers immediate access to tables. All access could be controlled through the SPs.

Advantage: the DBA can also add behavior the application does not worry about. For instance, storing a modify date on each row.

Only a couple of reasons I personally use saved methods solely when building programs:

  • Saved methods could possibly be the interface involving the application and also the underlying database. By doing this, the server which the database resides, that is usually more effective than the usual desktop machine, may be used to perform more complicated methods.
  • If you want to alter the structure from the database, that can be done having to break the application if saved methods are utilized because the interface.
  • While you write, saved methods contain precompiled and pre-examined SQL.
  • It's simpler to do complex procedures with saved methods compared to SQL produced through the client or GUI.