Allow me to lay out a story that should sound familiar:
A couple of coworkers are discussing a bug in the software. In this particular bug’s case, it concerns an icon that appears in a column of a datagrid. Each row in the grid is either supposed to have this little icon in a specified column, or not. It depends on the data. But that’s not really important, just background info.
What’s really important is that the icon was previously coded and tested and worked. It appeared under the correct conditions and was hidden when it should be. Now, all of a sudden, it wasn’t working. We get an e-mail from customers saying, “The icon should show up in this circumstance, but it doesn’t.” Time to investigate.
While my two coworkers are hashing out exactly what could have happened, why, and how to fix it, I start thinking to myself, “There should be unit tests to catch this. This shouldn’t have happened. I’ll just go find the test suite and write a couple new tests to ensure the icon properly displays based on the criteria of the data.”
Sounds easy, right?
As I started digging through the specific class code and examining the unit tests, I notice that there are no unit tests for this functionality. That seems suspicious. Then I notice something else: there’s no code. All that exists is a method call to a service that returns a datatable.
As Alice did, I decided to drop through the rabbit hole and see what’s up.
When I get to the bottom I see a gigantic SQL statement – several hundred lines long. It is embedded in the application, deep down in the data access layer, complete with multiple sub-selects and even an SQL case statement! It’s got multiple parameters in the where clause. It’s the kind of thing that makes SQL admins all warm and fuzzy inside, I’m sure. The whole thing is then handed off to our OR/M, which returns a simple datatable. While it is not a stored procedure, it might as well have been – it only has one step remaining on the cliff before it jumps right off into the database.
And the killer is, it’s untestable.
The caveat here is that this particular stored procedure was written as a performance gain, because the nature of the data requested is complex in its selection. But still, I’m of the belief that there might be a better way to query the data than one giant stored procedure.
Because the minute you put business logic in a stored procedure you compromise the quality of your application. You introduce the capability for your team to make mistakes that are no longer easily caught by a regression suite of unit tests. More importantly, I think, is that you increase the probability of repeated bugs. And to me, repeated bugs are a cardinal sin of programming. Nothing makes a development team look dumber than fixing a bug only to have it crop up again, and again, and again. It makes the software look bad, and by proxy it makes the developers look incompetent.