Stored Procedure

Advantage of  Stored Procedure

• Stored procedures can be used to maintain data integrity and enforce database policy without relying on an external program to do so.
• the DBA can add behavior that the application doesn't care about. For example, storing a modify date on each row.
• You do not need to deploy to make a change.
• Much faster than dynamic queries
• Easier to expand a system

Dis-Advantage of  Stored Procedure

• Debugging is hard.
• Source control can be a pain.
• 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.
• Refactoring is harder. Renaming or changing where the store proc is might produce a bad effect.