Posted by M A Srinivas on 05/29/07 12:06

On May 29, 4:37 pm, i...@vanoordt.nl wrote:
> Using coalesce is the same sort of solution as using isnull. It
> doesn't behave as my requirements state. In particular, the result
> will be 0 if all inputs are null. It is required that the result be
> null.
> Thanks anyway.

No. Did you test

Result will be null if all are null .
since
COALESCE(Col1, Col2, Col3) returns null and
0 + 0 + 0 + null is null
COALESCE takes more arguments and ISNULL only two

declare @a table (col1 int,col2 int,col3 int)

insert into @a values (1,null,null)
insert into @a values (null,2,null)
insert into @a values (null,null,3)
insert into @a values (1,2,null)
insert into @a values (null,2,3)
insert into @a values (1,null,3)
insert into @a values (null,null,null)

select COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) +
COALESCE(Col1,
Col2, Col3) from @a


2
4
6
4
7
5
NULL

[Back to original message]