My college Patrik Löwendahl wrote an interesting post about why he avoids sprocs. I would like to add my point of view on this.
There are several cases where a sproc can be usefull, for example if the underlaying datasource must somwehow be changed, we could simple change the sproc as long as the changes don’t affect how our apps wroks. By changing the sproc we don’t need to compile the application that uses it. But one problem here is if someone re-use the sproc. In this case we need to have well defined tests and also run all tests for ALL applications to see that our changes in the sproc will not made other apps fails. But this is more in a perfect world where everyone have defined a test, in some cases we don’t even have that luxury and we need to search in every possible code to see which code uses the sproc I need to change. Well if we some sort of repository where ALL code is located we can search for it in the repository, but maybe the some code aren’t located in the repository, in that case we will have a problem. So why re-use sproc? Well, don’t! ;) Well if we should have versioning support for our sproc this should probably not be a problem, but we don’t have versioning. In one of Patrik’s post Mats Helander wrote an interesting comment:
“Try to version sprocs and the dependency between them, you will certainly die of old age before you figure it out.
Mats also add the following comment to Patrik’s post:
“For example: I create a .Net component that uses Patrik's method to send sql to the db. Then I create a new version of the component with a new version of the sql. I can now use .Net versioning to select component and thereby version of the sql.”
He has a good point here, but can’t this make our code bloated with several components because of some changes need to be done to our SQL? Maybe the changes to the SQL don’t event change the way or apps will behave. In this case I think sproc can be useful, but as long as I can be 100% sure that the sproc will NOT break the applications that will use it. But if we need to use versioning, we don’t have a good solution for this when we use sprocs, so in this case Mats comment is good.
I moved away from sprocs for a long time ago and use OR-mappers that generate the SQL for me instead. Often when the underlying data source needed to be changed, the apps works because the mapping against the relational database was defined in a separate XML file, and the query I wrote was against my entity objects properties, not the fields and tables in the database. The first OR-Mapper I used (I wrote it by myself), I add the mapping as attributes to my entity object. The problem with this was that if the underlying data source changed, I need to open my entities and change the mapping and recompile my application. Most case when a we do a underlying changes in the data source, it’s a changed in our application, so in that cases the recompilation is needed anyway.
One problem with “ad-hoc” quires is that we will not get a compilation error if the quires were written in a way that it would not work. But why not test the query first to see if it works in the Query Analyzer? Well some quires can be tested but some can’t’. For example if we need to use several of conditions that should generate our queries. We will have LINQ in the future that will and can solve this problem. We can define a query that will at compile time fail if it’s created wrong. The thing that generated “ad-hoc” is a problem and we will first at runtime notice it, is not really true. If we write test that will test them during the development we can make sure they will work. But can we write tests that test all different conditions that can be used if we need to generate our query!?
I can’t say that I will NEVER use sprocs, it’s more a decision that I will make when I see what kind of project I should build etc. But mostly I will try avoiding sprocs, and because the OR-Mapper will generate the SQL for me I don’t really need to use sprocs. Well this was my point of why about this.
