Why should I use stored procedures?

There are many advantages to using stored procedures (unfortunately they do not handle the text/image types):

• Security - you can revoke access to the base tables and only allow users to access and manipulate the data via the stored procedures.
• Performance - stored procedures are parsed and a query plan is compiled. This information is stored in the system tables and it only has to be done once.
• Network - if you have users who are on a WAN (slow connection) having stored procedures will improve throughput because less bytes need to flow down the wire from the client to ASE.
• Tuning - if you have all your SQL code housed in the database, then it's easy to tune the stored procedure without affecting the clients (unless of course the parameter change).
• Modularity - during application development, the application designer can concentrate on the front-end and the DB designer can concentrate on the ASE.
• Network latency - a client on a LAN may seem slower if it is sending large numbers of separate requests to a database server, bundling them into one procedure call may improve responsiveness. Also, servers handling large numbers of small requests can spend a surprising amount of CPU time performing network IO.
• Minimise blocks and deadlocks - it is a lot easier to handle a deadlock if the entire transaction is performed in one database request, also locks will be held for a shorter time, improving concurrency and potentially reducing the number of deadlocks. Further, it is easier to ensure that all tables are accessed in a consistent order if code is stored centrally rather than dispersed among a number of apps.