I've been managing a small development team for a few months now and one of
the more interesting comments has been that I think like a DBA, not a developer.
This usually comes about whenever I offer a solution that is more sql based than
'code' based. This article will take about one particular instance. Will be
interesting to see what comments are posted!
Recently I was doing one of my semi daily walk arounds, seeing if anyone needed
anything, were on track, etc. Usually I stop in for a couple minutes, move on,
then back to my office to do my own work. I noticed one of the developers
writing code that I think you will all recognize, so I'll just present a vanilla
representation - translate to your language of choice:
if rs.fields("lastname") is null
somevar = ''
Basically you've retrieved a value from a table into a recordset, datareader,
whatever, and you're assigning the results to a variable or assigning an empy
string to the variable if the value is null. In essense, you want a string
value, you don't want a null.
This isn't uncommon by any means. DBA's love nulls (well, those of us who
believe in allowing them), developers don't find them as useful in program code.
Typically char/varchar/text values that are null become empty strings, null
integers become 0, null dates become 1/1/1900 or something like that. Now keep
in mind I'm not saying that developers don't understand nulls or ever find a use
for them, but I think most common app code tends to convert them to something
Looking at the code lead to a whole different discussion, but essentially
circled back to me asking if you're going to get rid of the nulls, why not do it
'upstream', in the stored procedure?
Basically if I were trying to accomplish the same task, I would change the
stored procedure to look something like this:
select isnull(lastname, '') as LastName from sometable where ....
This eliminates the possibility that I can get a null back at all.
Conversely, if I were writing the value back to the db, I'd typically do
something like this:
update sometable set lastname = nullif(lastname, '') where ....
The last thing I want in the db is empty strings unless they truly mean
something. In both cases I'm doing the work on the server, as close to the data
as possible. To me, this means that from that point on I don't have to worry
about the values, I can just work with what I get.
Is the SQL technique 'better'? Performance on either approach is close enough
to being the same to worry about and too small to bother with testing it anyway!
Quality? I'd say once tested both work equally well. I think the SQL version
results in less code written fewer times.
As we continued the discussion, we finally got down to bare metal - should
you put any code besides insert/update/delete/select code in a stored procedure,
violating the rules of n-tier design? I don't know that I'm qualified to speak
on what the rules are or should be, but I think when it comes to a database you
have two choices. One is treat it like a dumb data store, keep your procs as
simple as possible or even use dynamic sql exclusively, leaving yourself the
ability to connect to whatever type of data store your customer may elect to
use. The other is to leverage the strength of the platform, even if that means
breaking some of those 'rules' about what code should reside where. I like code
with as many layers as possible, stored procedures are a powerful layer that I
like to fully exploit. Still, if you're sticking with the rules, that does null
out the isnull/nullif scenario? Or does it?
Ultimately it's about knowing that there are different ways to solve a
problem and understanding when best to apply each. A lot of it is style, but the
style has to be based on merit, not just 'I like SQL' or 'I like code'! Talk to
your developers about this, how do they handle nulls? Do they know isnull and
nullif? Throw them a curve, get them to look at the coalesce function. Get them
to think about the power of the db platform and how it might change how they