A case AGAINST using Stored Procedures (and Triggers)

Many programmers are taught (and blindly follow) the "Stored Procedure" principle: If you have to insert/update/delete in a database table from a Java or .NET application, it is desirable to write stored procedures that do the insert/update/delete. The reasons usually given are:

1. Better Performance. The database server parses and compiles the stored procedure text once and re-uses the compiled string for later uses. This applies of course if you are using parametarized sql and you are not (god forbid!) using string concatenation.
2. By using a stored procedure, we allow the database server to also handle business logic, thus relieving the client site from processing.

This above reasons hold water for any RDBMS, albeit Oracle, MS SQL Server, Sybase, etc.

I would argue against using Stored Procedures. The main problems I have with the approach are:

1. The performance argument does not hold water at all. The database server will take the same resources and time to compile the stored procedure sql text, or the plain insert/update/delete sql text. The trick here is to use parameterized sql and not string concatenation.
2. The database is supposed to handle storage, not business processing. It's just not meant for programming logic. That is why we have the "Middleware", to handle our business logic.
3. By moving business logic to a stored procedure, then you are tying your business logic to procedural languages, and you are not taking advantage of Object Oriented programming concepts and techniques.
   The desired approach is to have all your business logic processing in the middle tier, or the client if you are working on a 2-tier system.
4. What about triggers? These are even worse. In my opinion triggers should NOT be used at all in an application. The only reason to use triggers is to get a new sequence number, and this applies only to Oracle. By having your logic dispersed in stored procedures, triggers and client code then you are decreasing the maintainability of your application.

To summarize, my advice is:

• Use plain insert/update/delete statements to update your database from your middleware/client code.
  Do not even create plain stored procedures that just do the insert/update/delete. In the future someone will have the bright idea to start writing business logic code in them. If they are there, they will use them.
• Use Java or any other Object Oriented language to encapsulate and capture ALL of your business logic.

By following the above, you increase the maintainability and extensibility of your application.