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 = ''
else
    somevar=rs.fields("lastname")
end if

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

'friendlier'.

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

solve problems.