Stored procedure: why use & why not use

Use Stored Procedure
● Reusability
– Avoid rewriting sub queries and improve readability.
– If you can't store a query in a library that all the applications can access, you can put that query in a stored procedure.
● Separation of duties
– You don't trust non-DBA's to write queries.
● Data integrity
– Use triggers or constraints to prevent bad data from entering.
– Run several interdependent queries in a transaction in a single stored procedure.
● Event handling
– Log changes.
– Notify other systems of new data.

Not Use Stored Procedure
● Views may be all you need.
● An object-relational mapping (ORM) can help write queries safely.
● Difficult to version control stored procedures.
● Software rollouts may require more db changes.
● Could slow software development process.