I’ve never really liked stored procedures. I always put it down to the fact that I’m a programmer, not a database person. I like keeping the functionality in the code. The database is just a place where objects go when they sleep.

My current client uses stored procedures for all database reads and writes. It probably made sense when the code was ASP, but Java JDBC code has all the expressibility you could want.

Today, I learned a new reason to not like stored procedures. Someone working on a stored procedure has broken the checked-in DAO code. Until they finish with the stored procedure, and check in the associated DAO changes, everybody using that database is dead in the water.

I suppose you could look at this as a strike against sharing a common database among all the developers, rather than against stored procedures. I could accept that.

It’s also a strike against making live changes to the database rather than scripting them (and checking the scripts into version control). Many times in my career I’ve come across the attitude that stuff in the database doesn’t need to be versioned–it’s just data. My take is that if it can affect the program execution, it’s code.

Ultimately, that’s the problem. The currently checked in code is out of sync with the current state of the database. One is version controlled and one is not. There’s no way to roll back the database change and keep working. There’s no way to go back to a known good state, and that’s bad.

It doesn’t have to be this way.

—-

I’ve tried to clarify this posting where it was misunderstood. I don’t mean that different projects should use different database instances until a big-bang integration. I mean that developers should have their own sandbox instance, so they can roll out changes to the schema in concert with changes to the code. I rarely see this done, because it does mean scripting both the database changes and any data necessary for functionality. When it is done, it pays off in spades. A little discipline in development practices goes a long way to making development easier and faster. Like working out at the gym, however, it’s difficult for people to get over the hump to the payoff.

—-

This posting is really about versioning and keeping all parts of the project in alignment. There’s a secondary question of where the business logic should reside. Some people say that stored procedures in the database is ideal, because it’s a central shared location. I find that the downside to that is stored procedures are necessarily procedural, and you lose the flexibility of object-oriented development for handling more complex situations. What do you think? I welcome your comments.