John Wood

Another Stored Procedures Argument

It's easy to think there are two types of programmers in this world - those who are for using stored procedures and those who are against. New and rehashed arguments come up every so often, and the most recent emergence here isn't any less thought provoking.

However I'd like to offer a different stance. I think both Eric and Jeremy are right, but for reasons they perhaps don't see.

Actually I'd like to offer a couple of different stances. And the first is more simple and pragmatic, and always comes to mind with such deep rooted, religious arguments.

You see, the measurement of an application's success is how well it has served its users and the company that pays the programmers. Period. The variables that go into this equation are plentiful - and range from the skill set and cost of the programmers, to the complexity of the application and potential scope for expansion.

Every situation is different. If your key developer prefers writing T-SQL to C#, then I think you can justify the amount of sprocs in your system. If you have someone with more C# experience and little to no T-SQL, then I think your best bet would be to write the app using simple dynamic SQL statements and do the logic in C#.

In a lot of cases it's purely preference and I don't think there's anything wrong with that - they can both get the job done. If the person allocating the company budget could see the arguments about whether to sproc or no sproc, they probably would have something to add and it wouldn't be an extra zero.

Now for my second perspective I'll get a little deeper.

If you look at Eric's original post you can pretty much replace 'stored procedure' with 'data tier' and the same points he makes hold true. Data tiers offer more security (when distinction between tiers is both physical as well as logical). They protect the UI and business tiers from change. Data tiers can be developed in C# - as they would if Jeremy were architect. Data tiers can also be developed in T-SQL and can live on the SQL Server as stored procedures. They are both data tiers and both offer similar advantages.

However I'd argue that C# and ADO just aren't very good at describing data query and manipulation. Perhaps Linq will make this better in the future (or perhaps not), but right now C# just isn't very data-oriented. You wouldn't describe Beethoven's Symphony No. 9 in algebra, you would describe it using music notation on a score. And for the same reason, using a general purpose imperative language to describe data manipulation and queries just isn't the best choice.  Set and array based languages are far more suitable, can more accurately and consisely articulate the intention and can thus be far more readable and easier to maintain.

Now to address Eric's second point about T-SQL not being portable. That's really not the biggest weakness. The biggest weakness is that T-SQL (the language) sucks. It's dated, syntactically retarded, stagnant and feature deficient - resulting in a spaghetti of select statements and temporary table constructions for an operation that could look far more concise.

Also his statement about dynamic where clauses is just puzzling. In my applications, at least, the user has a lot of flexibility in determining the filter that is applied to what they see, and this has a direct affect on the where clause that ends up being exected by the data tier. It's really not difficult at all, and effects nearly every query.

This is the reason I personally don't use stored procedures. I do always have a rich data tier, and this rich data tier is nearly always written in a declarative language. Effectively it's a domain specific language but the domain is that of data access.
.
The problem occurs, though, when you start putting business logic in your data tier. Any business logic written in T-SQL is in the wrong place. Delineating business logic and data access logic isn't always an easy thing (is any kind of domain-specific decision business logic?), and it can be excused in minor offenses, but often enough stored procedures are plagued with complex business logic that just shouldn't be there.

Putting business logic into stored procedures is inexcusable in all but the simplest of applications with no life expectancy. I'm almost certain that both Eric and Jeremy would never endorse such a crime, but much of the time people look at Pro vs. Anti Sproc arguments as justification for advocating such an abuse.

I honestly hope that T-SQL will one day get replaced with a new, more contemporary query language (perhaps XQuery? well perhaps not), or perhaps get the upgrade it desperately needs. But either way I think we can all agree that data tiers are a good thing regardless of what language they are developed using.