Handling NULL values in SQL case statements while inserting or Updating Records

I am working on this massive data import project. Reminds me of those old days, when lots of stuff was done at the SQL level and and .NET took care of the UI Side. LINQ spoiled me to do all the SQL stuff using LINQ to SQL.

Anyway, working on SQL console is a lot of fun. Especially for big data imports and exports, that's the way to go. Feels like you are more so of a DBA!

NULL values are always tricky to work with. I came across this problem where I had to write a case statement where if the value is NULL, update the 'remarks' field, else append the value to the field:

Here's what I was doing:

update [tablename]
set remarks=case remarks when null then 'source:ms.csv'
else (remarks + '; source:ms.csv') end
where email='xyz@abc.com'

Apparently, the field was'nt updated since the remarks field was blank for that record. Looks strange!

After doing a little research and trying a few conditions, here's how it worked:

update [tablename]
set remarks=case isnull(remarks,'true') when 'true' then 'source:ms.csv'
else (remarks + ' source:ms.csv') end
where email='xyz@abc.com'

Note that the second parameter should be implicitly convertible to the first parameter of isnull.

UPDATE: April 2012
For numeric/decimal/int columns, this would be it:

update [tablename]
set totalcount=case isnull(totalcount,0) when 0 then 1
else (totalcount + 1) end
where email='xyz@abc.com'

Check the microsft documentation here for details.

Thats how it goes! You can take care of bigger chunks of data, but its the little things like those NULL values that drive you crazy!