Business logic in stored procedures

Last week one of those classic discussions started after an e-mail which talked about using business logic in stored procedures. The email came from a guy who was working as a subcontractor for another firm. In this firm they use an Oracle database (... the Oracle guys always seem to be guys who bring up this discussion, must be because of their background) and they have also a lot of experienced PL/SQL programmers. They put forward the statement that it was better to store all of their business logic in stored procedures.

At first one of the tech guys seemed to agree, together they made the following statements:

If you put all of your business logic in stored procedures, you will get less network traffic since all calculations are done directly within the database

Putting business logic in stored procedures gives better performance

Higher flexibility in modifying code, you don't have to recompile anything,...

Lets say, that I definitely didn't agree and neither did some of my coworkers, together we decided that it's better not to use business logic in stored procedures:

Business logic in stored procedures is more difficult to develop, especially since you loose a lof of the benefits associated with OOAD

Stored procedures are hard to debug

Version control on stored procedures is a lot harder to maintain

Stored procedures are harder to test, ... did anyone try to write unit tests for stored procedures

Performance is often given as a reason for putting business logic in stored procedures. This is correct if you just use typical datadriven statements, once you start using loops, case blocks and if statements you will however experience a significant performance degradation. (So as a rule of thumb, don't use stored procedures for logic which needs these types of code)

I definitely support using stored procedures, they are a very usefull when developing applications, however only use them for things they are meant to be used for, datadriven operations. All other logics should be located in components outside of your database. (Well, maybe this will change once we an write managed code inside SQL Server 2005 - but lets wait and see how this code will perform.)