I don’t know if there is a good way and a bad way of using Stored Procedures. My experience with them so far has been pretty limited. I’m sure that there are wonders that can be achieved with them. At least they should be pretty efficient at cutting down data transfers between server and client, in some scenarios.

But the way they are used sometimes makes me wonder. Does every single SQL query have to be a stored procedure?

The web application I’m working on right now uses stored procedures for all CRUD database accesses; every single select, insert, update and delete is a stored procedure. And moreover, all stored procedures check session data, verify data, update tables and perform random application magic.

So, uhmm, yeah… things might go a little bit faster. Not noticably, I’m sure. But the dramatic downside of it is that it is impossible to change any data format, any table, any database access code without also having to change the stored procedures. The logic becomes stored both in the ASP code, and in the database itself; they are invariably and inseparaby tied together. The web application can not function without the code in the database.

This means a few things:

• You need to maintain an increasing number of items.
• You can’t switch the backend easily since the backend is part of the code.
• You become tied to one single database vendor.

For developers, we typically achieve the highest development speed when all the code needed to be changed is conveniently located in one, single place. If every little change requires changing code in a number of different locations, a very tangible inertia sets in. And with every additional location, the inertia grows exponentially.

The number of editing locations can be many. Front and middle-tier COM objects, stored procedures, XML files for language definitions, other definitions, configuration, several ASP files all depending on each other…

I feel that I am ready to formulate the First Law in an upcoming High Mobility Development methodology:

The time and effort required to implement a feature is exponentially proportional to the number of disparate items (files, data stores, deployment locations, databases) that need to be changed.

The inverse is of course also true: By keeping the number of items low, it is easy to achieve a rapid implementation of new features. And that is the essence of High Mobility Development.