Problem with CASE Expression

Hi, all here,

I have a problem with CASE expression in my SQL staments.

the problem is:

when I tried to just partly update the column a , I used the CASE expression : set a=case when b=null then 'null' end

the result was strange: then all the values for column a turned to null.

so what is the problem tho?

Thanks a lot in advance for any guidance.

Is it something like this you're trying to do?

create table #x ( a int null, b int null )
go
insert #x
select 1, 1 union all
select 2, null union all
select 3, 1 union all
select 4, null
go
select * from #x
go

a b
-- --
1 1
2 NULL
3 1
4 NULL

(4 row(s) affected)

update #x
set a = case when b is null then null else a end
go

select * from #x
go

a b
-- --
1 1
NULL NULL
3 1
NULL NULL

(4 row(s) affected)

drop table #x
go

/Kenneth

|||set a=case when b IS null then 'null' end|||The issue is with your comparison of the column value against NULL using equality operator. By default, <any non null value> <> NULL unless you set ANSI_NULLS to off and this affects few operations in the server. You can check the Books Online for more details. The recommended syntax is to use the IS NULL or IS NOT NULL clauses for checking NULL values.