Should I Place My Business Logic In Code Or In Stored Procedures?

I see this age old question asked on forums, LinkedIn conversations, interviews, and in general developer discussions. To tell you the truth it has been discussed 1000 times before and I am going to add one more to the pile here.

This is a great debate/conversation, and I have been a part of it several times myself as well. I can honestly say there is no 'right' or 'wrong' in my opinion (OK maybe there is but let me be a little PC here as to not to offend those DB folks right from the start), but only 'pros' and 'cons' to each method. I think both have their advantages and disadvantages, and no option really screams out as the obvious choice on the surface unless there is say some significant performance advantage one way or the other. However I have my bias on 1 side of the argument as I will detail below.

In my experience this is usually who goes on each side of the debate: people with a stronger or more prominent SQL/Database background will opt for placing all the logic in the database where as true software engineers will tend to place those rules within the application and apply the appropriate architecture, design patterns, and frameworks to organize and describe them.

At the highest level Database folks will argue to place that logic in stored procedures or the database because then you can "make changes on the fly" without the hassle of "code recompile and pushing out new releases", where developers that want that logic in code will argue TSQL is such a limited language to debug and express complex business rules that can be evaluated so much better in code.

Well as far as I am concerned that is about as far as that side's (Database crowd) argument goes for me. While their argument is valid, recompiling and redeploying an app because of rule changes is typically not an impossible task. In fact with today's modern deployment options regardless of technology being used this argument is not as strong as it used to be.

In fact the whole idea of "making changes without redeployment" or "changes on the fly" is a bit skewed to begin with. Code of any type should never be changed in a production environment without being tested. Managed code is more likely to be tested using unit tests or at a minimum running the application through some end-user tests before deployment. The temptation to change a stored procedure without testing it fully is ever present, and without a stringent environment with DBAs locking down the database, this option could be exercised all too easily and is a bad idea.

As a developer I tend to try and place these rules (business rules, calculations, math, etc.) in .NET managed code. TSQL is not the easiest language to explain and implement complex business rules and is also more difficult to debug and test. My philosophy is typically to get the raw data back to the code and then manipulate it from there. You have so much more power with the .NET framework and managed code to explain the rules in code than with TSQL. While it is possible with TSQL, have you ever seen one of these 500 line stored procs with a gazillion rules that some database person was flexing their muscle at? Yuck! We are in the business of writing well formed code using OO principals and this is just not possible using TSQL and database languages.

Which side do I fall on? That's easy. I am a developer and engineer at heart and I believe in using the right design and architecture, and that those business rules (or a majority of them) should reside within the application. What's the database for? Getting data and executing some logic, but the brain of solving the business problem for me is not going to be within a stored procedure, view, or user defined function. The only time I flex a tad on this is with 1 off custom queries for say reporting where the Object Model does not currently support the data needed to create the output. In this case I might extend the logic a bit on the database side. If I can extend my Object Model to support the reporting without an extreme amount of work then by all means I will do it. However, when at all possible I keep any business rules or logic within the application.

So the answer is not always so straight forward, but hopefully this post help you think about where this logic should reside (......in the code. Wait, did I just say that!?!?). Now go ask this question on a SQL forum or blog and see what response you get. It will probably be along the lines of... "Put it all in Stored Procedures!!" Have fun coding :P