Putting your Logic in Stored Procedures OR How to Create a Maze of Unescapable Complexity

I almost agree with you on this, however I think it is important that we define "business logic" in this case. Looking at your example, I agree that it makes no sense to define a stored procedure that returns all the users that registered in the last 7 days as this tightly couples the database code to your business logic. HOWEVER, the database is going to be able to sort through all the users stored in it to get only those that are needed MUCH faster than any code I would ever be able to write. Databases are highly optimized for situations like this; most user code is not, nor should it be. Therefore, I would argue that in this particular case, it would be better to define a stored procedure that is capable of setting parameters for what users it should return, however the implementation logic for this query correctly belongs in the database. This would allow the business logic to determine what users it wants to load and then pass this into the stored proc as a parameter and allow the database to do what it does best and then only return those entities that are needed by the business layer to process.

This brings us to what I refer to as "Jeff's second law of programming:" Let the database do the work; it is far better at it than you are.

While I agree with your 2nd law, I don't see the need for a stored procedure to perform that operation.

With NHibernate (or another ORM) for example you can easily build a query that will return the X amount of users as efficient as the stored procedure will.

Hi,

For some reason my comment was not posted earlier. So, I am posting it again.

I strongly agree that domain logic should be placed in the domain and not in the stored procedures. But we need to understand that earlier systems were not created using any O/R mapper. Also, I agree that in big systems run by big organizations you have very little knowledge of the system. Each developer is responsible for his own module. And making a small change require a developer to climb the approval chain. But there are systems where a developer can change anything in any layer. This is usually seen in small systems with few developers.

Our system requires immediate change without restarting the application. There are ways of making the change using XML files but writing T-SQL in XML is not a good idea. So, in those cases the logic in SPROC usually benefits the application.

My conclusion is that sometimes there are cases when putting the logic in SPROC is more suitable for the given scenario.

Whether you use parameterized queries, dynamic SQL, ORM tools, or a simple config setting there are scores of ways to make the number of days in this example controllable at runtime. Changing the behavior of your application is a matter of design, and if you know your functionality domain well, you will know how to design in flexibility for the things that may change (7 days vs 14 days), and optimize performance and stability for the things that probably will not (users enrolled that have a middle initial of F vs. D).

In general, I don't prefer to put logic in stored procedures, and there are several other reasons why.

1) Database agnosticism - What about when your client gets fed up with Oracle license fees and wants to switch their DB platform to EnterpriseDB or SQL Server?
2) Scalability - The more logic you put in the database, the more you load up the most difficult tier to scale -- the data tier. It is much easier to add an application server if the system grows quickly and becomes sluggish. I completely agree that you should let the database do what it does best which is read and write data, not make business logic decisions.
3) Complexity of Change Control - When all of your logic is in code, you have many more options for version branching and history comparison. If your logic is split between code and database you create a much bigger versioning problem for yourself.

If your system requires IMMEDIATE change without restarting then you are taking agility to an unhealthy extreme in my view. You have to draw the line somewhere, and that line should be cognizant of the fact that continuous integration allows us to make a responsibly versioned code change, run it through a proven build, test, and deployment cycle in a matter of hours. If your users can't tolerate a couple of hours for a requirement that they just dreamed up after you are already live then you are on a chaotic road to troubletown.

IMO, most programming languages (compared to SQL) are more expressive when it comes to describing business logic. It seems to me that writing a few lines of readable C# is much better than writing a 100 line block of T-SQL that accomplishes the same thing. At least in the long run. I have had to maintain a system or two written in the manner you describe and it was not fun. Sure, it can be flexible (once you have spent untold hours figuring everything out) but it is hardly what I would call maintainable.

As always, it depends on the nature of the business logic...

Any reconciliation system that takes thousands of records from one system and does a full outer join on thousands of records from another system, an ORM system like NHibernate doesn't really help you. Yes, I could write said query in HQL but syntactically - in my humble opinion - HQL is only marginally easier to read than SQL in most cases. Further, if I'm going to go to the trouble of having to actually write a query (as opposed to using domain objects), I'd almost rather write it in SQL as a sproc because at least then I don't have to ship a new assembly if I want to make a minor change to it.

I totally buy into ORM, I just don't think it's a one-size fits all solution.

Scott: Your gravatar is SCARY.

Mark: If you really need to do a full outer join across two separate data stores, you're going to run into perf problems no matter what route you choose. I'd often opt for duplicating the data via a scheduled service if I could get away with it so that I could work with 1 single database in my project. (Of course that's not always possible)

You mention HQL in terms of readability, and that's not where I see the benefit. The benefit of HQL is that it's a familiar syntax to SQL, so it should be easy to write for most of us. That SQL-Like syntax is more lightweight than the readable Criteria queries. In the end the only benefit of HQL is that it gives us an object graph as a result, instead of flat record sets.