Stored procedure has been known to have performance advantages in comparison to inline sql statement, such as:
- Pre-parsed/pre-compiled SQL statement: also cached for sub-sequent use.
- Pre-generated Query Execution Plan. Query Execution Plan is a set of steps that is used by the database to execute sql statements.
- Reduced Network traffic: SQL statements can be executed in batches.
- Improved security: user can be given permision to execute a stored procedure without having any permission on the underlying data/table.
For all we know DBAs love stored procedure and preffer to use them instead of inline sql statement, but do we really gain much from using stored procedure? Developers may not have the maximum productivity when developing application driven based of stored procedure. This is due to the fact that it's not that easy working with stored procedure. They're painfully hard to debug with those non-descpritive error message, and the fact that they tend to hide business logic makes application to be less consistent.
Having said that, placing your business logic in the database is not entirely a bad idea, especially in a case where your database is used by more than one application. To have a centralized place for your business logic means that there is only one code base to be maintained/managed. Changes in business logic will need to be applied only to the stored procedure, not to every application that talks to the database. This case of course does not apply to database that is used by only one application.
There are also arguments saying that with modern databases, performance advantages that stored procedure has may be negligible. This is right, but not always. Yes it may be negligible, but only for simple queries. For larger and more complicated queries, the performance difference may substantially adds up (time to parse/compile sql statements, and generating the execution plan). More complicated queries may take as slow as few seconds to build their execution plan, when stored procedure is used this will be cached for sub-sequent use.
In conclusion, using stored procedure is definitely a good practice, especially with those performance benefits. But for simple queries, it may just be sufficient to use inline SQL statement, as those performance benefits will be negiligble in modern database. Who knows years from now, maybe stored procedure would be completely redundant. Moore's Law lives on!!