How modular should your SQL programs be? Over time my answer hasn’t changed much. What has changed is my reasoning.
My original position was based on the behavior of SQL Server specifically. In older versions, if you had branching in your query to do different kind of work based on parameters or data, and you included the implementation of each of those branches in a single stored procedure, the stored procedure would be recompiled each time the branching changed from one execution to the next. This behavior has been optimized in later versions of SQL Server, allowing individual branch statements to be compiled individually, and reused while procedure cache is available.
Before statement level compilation was available, you would create a separate routine implementing the logic for each branch, and it would be compiled separately. It maintained its own procedure cache, and you would have the same performance as today. This made your procedure code more modular.
Today, I find myself somewhere in between, depending on the complexity of the code being branched. If the code is more than a simple query, I may break it out into a function, table value function, or another stored procedure simply for the purposes of unit testing. Breaking this code out allows me to execute that individual logic all by itself without the clutter of the branching logic of the calling procedure. This is a great advantage. The advantage increases as the complexity of the routine increases.
Why wouldn’t you want to break things out? In SQL Server, there are not many components you may write that are used in multiple places. Most of our procedural code is used in a single place. Breaking out the code can make it more complicated for another developer to understand. It also makes management and migrations more complicated due to the dependencies of one object to another. That being said, I have found that if I don’t need to unit test a routine, and there is no branching logic, I may not break out an individual logic path, to keep things simple and cohesive.
The reasoning for testing to me is a more compelling reason to break out code. It is not data engine specific so it can be applied to any relational database supporting procedural code. Theoretically, you can apply the same logic of separation to middle ware if that is the location of your business logic because your data engine doesn’t support procedural code.
Perhaps the testing principle will assist you too, even if you are not using automated unit testing. You may still need to do your own testing to assure the logic is as you wish.
Cheers,
Ben