Take the 2-minute tour ×

Would like to get a list of advantages and disadvantages of using Stored Procedures. The main advantage of SPs seems to be precompiled and an abstraction of data from the application. Give me your thoughts....

share|improve this question

18 Answers 18

up vote 7 down vote accepted

Correction: Whether they're precompiled depends on the database. In SQL Server, for instance, they're not. Stored procedures and parameterized SQL are both compiled before being run. A stored procedure can sometimes reuse an execution plan if a corresponding one exists...but so can parameterized SQL.

Edit: Here's what MSDN says about it:


share|improve this answer

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

Also groups all queries at the same location, making it easier for DBAs to see how the database is queried and optimize it accordingly.

Disadvantages: May not be the best place to put complex logic. However, following the idea that complex logic belongs in application code and not in stored procedures, stored procedure become simply CRUD operations (each table has a "Create", "Read", "Update" and "Delete" procedure). In that case, stored procedures don't add any value to the application, they only complexify maintenance and become waste.

Queries are all grouped together, so it's harder to see the context of the application where they are being used. Analyzing the impact of a change is longer, and doing the change is longer as well.

Therefore: use stored procedures to encapsulate complex queries (complex joins, complex where clauses, ...). But don't use stored procedure for complex application/domain/business logic, and don't use stored procedures for CRUD either. So stored procedures should be used in a minority of cases rather than be the standard tool for all queries in an application.

Group code (including queries) to achieve "functional cohesion" instead of grouping by tool/technology. To allow a DBA to optimize a database based on how it is being queried, use a profiler.

share|improve this answer
-1 And queries and views do not provide abstraction layer? –  Tegiri Nenashi Apr 20 '11 at 22:09
Queries don't provide an abstraction layer over the database structure, since you have to specify the table names, column names and key names for the joins. But I agree about views, they can be another way to make an abstraction layer over the database structure (both for querying data, and for modifying it through "instead of" triggers). The major difference between the two abstraction layers (stored procedures vs views) is that stored procedures are "verbs" while views are "nouns". –  ckarras Apr 21 '11 at 2:09

By using SPs, you also avoid having to give users direct access to tables. All access can be controlled via the SPs.

share|improve this answer
+1 This is a big one. –  John M Gant May 27 '09 at 18:28
not that big, since it can also be accomplished with views or other database platform specific access control tools (roles,...) –  ckarras May 27 '09 at 18:36
No, it still is a big advantage, regardless of other means available. –  Gromer May 27 '09 at 18:50
@Gromer: What advantages do stored procedure have over these other available means? Is there something they do better? Something that can be done only with stored procedures? –  ckarras May 28 '09 at 0:42
@ckarras: Are you talking about only letting the user access Views and only using "INSTEAD OF triggers" in the views? I have never seen such aproach in action and seems a hack in comparison to just using stored procedures... Also IT IS less powerful and secure than just letting the database user just have the permission to execute very particular actions (stored procedures). –  Durden81 Oct 11 '11 at 9:07

With the current .Net 3.5 framework libraries, I would use Linq to perform most database operations. There might be places where SP makes more sense. But Linq has provisions to run an SP too.

On the topic of disadvantages of SP, check out the following link - an interesting analysis. Check the blog post's comments too.


share|improve this answer
Great link. Interesting ideas in both the post...but also some great point-counterpoint in the comments. –  Beska May 19 '09 at 12:52
Yeah I keep going back to reading that link, everytime something new I learn –  Vin May 31 '09 at 0:33
Its nice to see something I wrote a while back still beneficial. –  SpoiledTechie.com Sep 8 '10 at 14:25

Advantage: Stored procedures can be used to maintain data integrity and enforce database policy without relying on an external program to do so.

Disadvantage: Can make debugging more complex. Can also be sensitive to being dropped during copy operations, if not done correctly.

share|improve this answer

Just a few reasons I use stored procedures exclusively when building applications:

  • Stored procedures can be the interface between your application and the underlying database. This way, the server on which the database resides, which is usually more powerful than a desktop machine, can be used to perform more complex procedures.
  • If you need to change the structure of the database, you can do so without breaking your application if stored procedures are used as the interface.
  • As you write, stored procedures contain precompiled and pre-tested SQL.
  • It is easier to perform complex operations with stored procedures than with SQL generated by the client or GUI.
share|improve this answer

Another disadvantage is version control, because some of the business logic is now in the database side. Can you easily roll back to v1 (one year ago) from v2 (now)?

A feasible solution is versioning the stored procedure names. But now the database is a mess with old and new stored procedures.

share|improve this answer
Surely this just means you are not treating your stored procedures correctly. They should be treated as source code and managed alongside your java or C# or whatever –  WW. Jan 20 '09 at 6:55
WW, I totally agree with you in principle. But is there a tool to support that? SourceSafe? CVS? Subversion? Git? –  yogman Jan 20 '09 at 20:47


  • Source control can be a pain.
  • Debugging is hard.
  • If you have a lot of functionality in procs it will making swapping between different database systems harder - It also creates more work if you want to support different database systems.
  • Developing stored procedures can be a fairly specialised task, especially as they get more complex.
share|improve this answer

Advantage: the DBA can add behavior that the application doesn't care about. For example, storing a modify date on each row.

share|improve this answer


  • Refactoring is harder. Renaming or changing where the store proc is might produce a bad effect.

  • Unit testing stored proc require code assistance outside the DB


  • You do not need to deploy to make a change.
  • Faster sometime
  • Easier to expand a system
share|improve this answer

Advantage: your database-related code is more likely to be written by staff who are interested in and skilled at database work.

share|improve this answer

Advantage: the operations team has a hook to monitor or fix problems in production.

share|improve this answer

Advantages: SP's are used to excute set of sql statements. Disadvantages: Debugging is complex

share|improve this answer

Another advantage can be in large enterprise environments where you could have multiple client applications & environments (such as web, desktop, reporting tools spread over different OS's) that use the database. For some business rule changes the change can be made in the database and this would be effective in all environments.

share|improve this answer

Advantages -

  1. Organized at one place (not sprinkled all over the code)
  2. Much faster than dynamic queries
share|improve this answer
-1, they're not necessarily any faster than dynamic queries. Sometimes they are, but often the performance is exactly the same. Not all stored procedures can be effectively precompiled. –  John M Gant May 27 '09 at 18:26

The advantage I see in using Stored Procedures over writing the same logic within application code is that it can reduce the number of calls the application makes to the database.

A stored procedure can take in its arguments and make different decisions and actions based on those arguments instead of returning a result to the application, and then the application making a decision and decide it needs to perform another action and make another database call.

The bottle neck in performance is almost always inter-process communication. I try to make the minimal amount of calls to the database.

share|improve this answer

It increases the load on server. If other applications or more than one application are using the same database server they become slow.

share|improve this answer
Why not create a database for each request? –  Andrei Rînea May 19 '11 at 13:28

Your Answer


