Stored Procedures aren't all that

I’m not sold on the very common corporate practice of requiring all database access to go through stored procedures. It’s not particularly efficient, it creates a lot of extra work for tiny benefits, and it cultivates code that’s duplicated and hard to find.

Now and then I check into The Daily WTF, partly because it’s funny and partly because it makes me feel a little better about some of the crazy code I’ve observed or been asked to maintain.

One in particular from about seven years ago amused me greatly, but not just in the train-wreck sense for a change. It addressed the very serious subject of putting all your database code into stored procedures. The comments are actually pretty illuminating.

Alex Papadimolous, actually being serious, wrote:

Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the “data layer” by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.

I don’t think so, if by “most information systems” you mean the typical multi-tier business and financial databases that most of us maintain in our day jobs. I find that the looser coupling is almost always theoretical; it breaks down when you have to look at code from a year or two ago and figure out what the underlying stored procedure isĀ actually doing instead of what you expect.

Continue reading “Stored Procedures aren't all that”

The W part of DTSTTCPW

When developing iteratively, what exactly constitutes premature optimization? When doing the simplest thing that could possibly work, what does it mean to “work”?

A couple of weeks ago, a question turned up on the BaseCamp site we use to coordinate one of my projects. One programmer asked what we thought of a certain calculation he was setting up on the database. It had to do with accumulating “rating” points of an item in a tree-shaped threaded discussion.

Continue reading “The W part of DTSTTCPW”

Access: Why not?

I don’t suppose Access is ever the only solution to a problem. But in my experience it’s frequently a reasonable one and it spares a lot of drama over acquiring and installing a database engine.

I blogged a few times recently about ways to make Acess databases do kind of what you want when you’re programming with .NET. There was this one about multiple JOIN syntax. Then this one about “parameter” errors. And finally this one about weird column names. Yuck!

Continue reading “Access: Why not?”

Programming with Access? Know this about column names

Does your Access database have sketchy column names with weird characters in them? Surround them with bracket characters so SQL will work.

Because it’s the beginning of the week, I’m again presenting more about programming .NET with an Access database.

Last week, I offered help with a confusing syntax error. Before that, I demonstrated the unique way Access handles JOIN. Today I have a tip on addressing poorly named columns in your code. Continue reading “Programming with Access? Know this about column names”

Programming with Access? Know this about "No value given for one or more required parameters"

Today, it’s more about programming .NET with an Access database. Last time, I explained the unique way Access handles JOIN. This time, let’s talk about a syntax error that masquerades as a missing parameter.

Continue reading “Programming with Access? Know this about "No value given for one or more required parameters"”

Programming with Access? Know this about JOIN.

Writing your own SQL statements against an Access database can fail in surprising ways. Here’s how to avoid one common problem: the three-way JOIN that won’t execute.

If you’ve ever implemented a small-scale WinForms .NET project with modest database needs, you’ve probably been tempted to keep it “simple” by using Microsoft Acess (.mdb) files.

Continue reading “Programming with Access? Know this about JOIN.”