The gist is that traditional mantra of putting all (or most) of your DB-related code in the stored procedures (SP from now on) is overrated. The reasons stated are not really convincing to me, but there are some good points made.
I'd like to add a bit more to the picture. Let's assume first that you do want to keep your code in stored procedures - they look like functions, so programmers are used to their invocation style, they are explicitly parametrized and hide the underlying DB schema, plus are separately securable. I don't think that problem lies in these facts, but in what follows from here.
If a project is reasonably complex, it becomes tempting to put all DB related code in SP, and just one step from there all business logic as well. Due to various reasons that Jeff listed (one of which to me is especially annoying - you can't debug SP the same way you do the rest of your code) this is not a Good ThingTM. The feature that made this possible is archaic evolution of SQL language to allow non-DB operations like type manipulation. You can slice and concatenate strings in SQL if you like, transform dates and whatnot. Why not add a bit more on top of that? But soon “a bit“ becomes a lot.
SQL as a language was not made for this*. Relational databases are based upon a relational algebra, a very formal and powerful theory. Using SQL for anything more than relational data manipulation is just calling for trouble. Don't compute values using complex formulas in SP, don't parse string values. Operate on tables, and spit out tables. SP are a line between your table based data storage and your true business logic, which is written in another language suited perfectly for general purpose programming tasks.
This is all nice and well, but practice shows that sometimes it very desirable to have general purpose processing weaved with relational manipulation. That's exactly why SQL evolved to allow such a use, by adding non-relational based operations. But if this is wrong as I claim, what is one to do?
Enter SQL Server 2005. It allows you to mix .NET code with the SQL code in exactly the right way. You can't all of the sudden start writing SP in C#, but you can make table columns of any .NET type you like, and very easily add and transparently debug code that will handle all type manipulation. That is enough. Keep your relational processing in SP, add .NET types for more complicated column types, and leave the rest for your C#/VB.NET business logic.
So the question is not if we need SP or not (I think we do), but where is the line between them and the rest of the (business logic) code.
*Actually, SQL is a language initially designed so that non-programmer types (i.e. accountants) can extract data from the DB themselves. But the underlying processing language is heavily relational algebra based, and SQL cannot escape being influenced by that.
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5