Disadvantage of stored procedures

databasedatabase-designsql-server-2005stored-procedures

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….

Best Solution

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.