Van Heghe Eddy (3/10/2013)
Using stored procedures has also the following benefits.Security : dba can determine who can execute what
Tuning : mostly stored procedures are written by sql developpers who mostly know to find the most performant ways to manage the objective at hand.
Modify : when bussiness logic changes there is no need to wait for the application developper but can be handled by all sql developpers
and in most cases because stored procedures are compiled code there could be performance benefits..
Hopes this helps you in your way of thinking.
Wkr,
Van Heghe Eddy
Security - can be also achieved without use of stored procedures (eg. via table & vieww access)
Tuning - the way you've put it is very optimistic! In my experience I've found too many cases where stored procedures were written by non-sql developers, without any consideration for performance...
Modify - that is interesting one! One of the reason ORM is used for system/application development is to avoid additional resources (eg. sql-devloper) required for making changes.
and the last bit:
and in most cases because stored procedures are compiled code there could be performance benefits..
Ad-hoc queries also can be compiled and cached...
So, all of the above alone cannot be reason for using stored procedures.
Nowadays, it's really depends on the chosen system/application architecture.