Friday 21 June 2013

Stored Procedure - Advantage of Stored Procedure - Disadvantage of 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
  • A small functionalists can be added in SP (Store Procedure)

Dis-Advantage of  Stored Procedure
  • Debugging is hard.
  • Source control can be a pain.
  • If you have a lot of functionality in store procedure 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 specialized task, especially as they get more complex.
  • Refactoring is harder. Renaming or changing where the store proc is might produce a bad effect.