Be careful with CASE and NULL statement

Checking NULL values in database can be a little tricky. It is very easy to trickle those if you do not provide correct syntax. The recommend way to check null is ISNULL(myfieldname,”). When using NULL in case statement, it might run without any error but will give you in correct result. Consider this example

Script to create #temp table. Note that # in front of tablename means it is temporary table. it will be automatically destroyed once you close you window.

create table #temp (
id int not null primary key identity,
A int,
B int
)

insert into #temp values(10,null)
insert into #temp values(null,10)
insert into #temp values(10,null)
insert into #temp values(10,null)

select * from #temp

Now lets say you want to pull column A value if it is not empty and column B value if column A is empty. You might want to write something like this which is incorrect

select A,B, C = case A when null then B else A end from #temp

This basically pulls column A value and does not bother about column B value at all. Here is the result

A	B	C
10	NULL	10
NULL	10	NULL

The correct syntax to populate column C with either A or B depending which one has value is

select A,B, C = case ISNULL(A,'') when '' then B else A end from #temp

Here is the result

A	B	C
10	NULL	10
NULL	10	10
This entry was posted in Tutorials/Tips and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s