Arguments for/against Business Logic in stored procedures

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.

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.

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.

"You can not integrate it into source control very easily." - if you put the code that creates the stored proc into a script that's version controlled, that objection goes away. If you follow Scott Ambler's agile database ideas, that's exactly what you should be doing.

Not all developers are good data modelers. I can think of horrible schemas created by developers who thought that a dabbling knowledge of SQL made them database experts. I think there's a lot of value to having developers working with DBAs and data modelers.

If only one application uses the database, I'd say that business logic can appear in the middle tier. If many apps share the database, perhaps it's better to put it in the database.

SOA offers a middle way: services own their data. Only the service has access to the data; getting to data means going through the service. In that case, it's possible to put the rules in either place.

Applications come and go, but data remains.

One more reason NOT to store business logic in sprocs - limited scaling abilities of the DB. It is very common situation where your database is your bottleneck, that is why it is a good idea to take as much load of the DB as possible.

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 are different kinds of "business logic". Consider partitioning it based on how it's related to other layers or services. Here are some rules of thumb from an MVC perspective:

a) In the database (stored procedure) if it's mostly data-related, and can be done with joins and relatively simple WHERE and SELECT clauses.

b) In the controller if it's mostly routing or dispatching related; that is, larger-scale UI flow control in terms of screen or resource selection.

c) In the model or view-model if it involves complex or intricate computations and/or conditionals.

d) In the view (such as Razor) if it's primarily a display issue, such as "friendly" re-formatting and relatively simple to implement. (If it's complex, consider putting it in a view-model.)

My rule of thumb (once I recognized what it was by thinking about the question) is that stored procedures should contain code that ensures data integrity within the database, whether the stored procedure prohibits certain data insertion, deletion or modification, or makes other changes necessary for data consistency. Business logic, especially logic that cannot be implemented in a handful of set-based operations, should be implemented elsewhere. Databases are not applications. Databases should be the ultimate authority for relationships and constraints, even if the business rules are also implemented elsewhere for, say, the provision of feedback in user interface code that reduces postbacks from a web server or eliminates otherwise unnecessary hits on a busy server. One can argue whether "data consistency" includes the result of complex processing of complex business rules, but I think it's usually clear once the context is understood. Not all business rules are implemented as data relationships or constraints. Not all operations in a stored procedure are faster than code running in a separate process, even on a process running on a separate machine across a network. I recently did a demonstration showing that many operations in SSIS, for example, (INSERT INTO () SELECT FROM) perform faster in SSIS running across a network on a separate machine than running in a stored procedure (that also inserts the results to a database across the network). This is an almost unbelievable result (where SSIS is faster than raw SQL statements), and demonstrates that the discovery of the best optimization of any performance issues comes from reality (testing) and not from logic based on only a few concepts. (We still have to make decisions on what to test by rules of thumb learned by experience.) (SSIS performed faster by automatically implementing multithreading and pipelines, using BULK INSERT even where it wasn't specified in the raw SQL statement, and sending batches of inserts on one thread while creating additional BULK INSERTs on other threads. In this instance, it performed about twice as fast as raw SQL statements.) When I used to teach programming and SQL Server courses, PowerBuilder users seemed to have the statement "Native drivers provide the fastest data access" burned into their tongue, and while it might be justified through additional (unrecognized by them) explanation, the thinking behind it is misleading.

With all this micro-services and micro business components ideology we are way ahead of questioning the right place to put our business logic.

Even though the ideology is well accepted we still have temptation and some instances where we ended up putting some decision making and business logic in database. It’s worth visiting detailed answer to why it should not be done, but at macro level I would advise thinking about one good reason why it should stay in stored-procedure and not in the application layer.

Having that counter thought process would always direct taking the right decision. Ask these questions before deciding:

1. What if down the line we change our database from SQL to Mongo?
2. What if want to expose API which takes the data (that database is providing) and apply this business logic on top?
3. Unit testing this business logic?
4. SDLC steps involved if we make change in conditions of this business logic?
5. What if we need another user input for the decision making in this business logic?
6. What if it requires high computation power (not data processing) and we want to be run off a separate process (or platform) ?

In all cases it would make natural choice to not put any logic (other than just data retrieval) in business logic.