Arguments for/against Business Logic in stored procedures [closed]

Question

What are the arguments for and against business logic in stored procedures?

Answer 1

Against stored procedures: business logic in programming space

I place a high value on the power of expression, and I don't find the SQL space to be all that expressive. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.

But it depends. If you have multiple applications interacting with one storage mechanism and you want to make sure it maintains its integrity and workflow, then you should offload all of the logic into the database server. Or, be prepared to manage concurrent development in multiple applications.

Answer 2

I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored proc at the same time.

My other main complaint is that SQL is just not very good at representing complex logic. You have no concept of scope, code tends to be copy-pasted because there is a less ability to reuse code (as opposed to an OO language).

You have to give developers access to the database to develop there. In many organizations I have worked at the data people are in a different world than the devs, with different permissions, etc. Keeping the devs out of the database in these cases would be harder.

Answer 3

I'm of the school of thought that says that as long as business logic:

• lives in one place
• where it is properly documented
• proper access is provided through services that can be loosely coupled
• through a published abstracted interface

I don't care if the logic lives in a stored procedure, in a J2EE middle tier, in a clips expert system, or wherever. No matter where you store our business logic the "law of conservation of misery" is going to guarantee that someone will say it was the wrong idea because component/repository X needs to be swapped out for technology/method Y.