45
Business logic should be encapsulated in one place. We can guarantee that the logic is always run and run consistently. Using classes that all activity involving an entity on the database must run through we can guarantee that all validation is run properly. There is one place for this code and any developer on the project can easily open this class and see the logic (because documentation can and does get out of date, the code is the only reliable form of documentation).
This is difficult to do with stored procedures. You may have more than one sproc dealing with the same table(s). Chaining multiple sprocs together so that the logic resides in only one gets unwieldy. That is strike one. How do you determine "What are all of the business rules surrounding entity X" within the database? Have fun searching thousands of sprocs trying to track that down.
Number two is that you are tying your business logic to your persistence mechanism. You may not store all of your data in the same database, or some may reside in XML etc. This type of inconsistency is difficult on the developer.
Validation is difficult to perform if the logic resides only in the database. Do you really call a sproc to validate every field on your data entry form? Validation rules and business logic are close cousins. This logic should all be performed in the same place!
+: SQL server sometimes optimizes the code
+: You are forced to pass parameters, which limits SQL injection issues
-: Your code depends on a single database (some dbs don't even have SP)
-: To change code you need to connect to database
-: Logic is not organized well
Personally I'm against it, but I had to use it once on a really busy website. Using SP in MS SQL brought huge benefits, but once I implemented caching those benefits were not so big anymore.
There is a saying...
When all you have is a hammer, everything looks like a nail.
In my humble opinion there is no one answer that will fit all circumstances. It seems to me that many people just assume that putting Business Logic in the database is always wrong.
A lot of work has been done to make transaction processing, especially bulk operations, very efficient when done on the database side. Also the code management in databases has vastly improved since most of the opinions against databases were formed.
I think it is wrong to consider the database server as just a persistence layer. If your processing activities are most efficient when done on the DB Server then do them there.
If not then do them elsewhere.
It all comes down to what best fits the application you are working on at the moment, the team with whom you are working and the customer who hired you.
That just my 2 cents.