Coalesce vs IsNull - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Coalesce vs IsNull

Author

Topic

Teroman
Posting Yak Master

115 Posts

Posted - 2002-02-12 : 11:05:26

Has anyone else noticed these behaving differently?

I spent hours (well it felt like it) going throught a pretty mad proc looking for the mistake, and changed and IsNull for a Coalesce so i could put another argument in.

That didnt work, so i took the third argument out, but being a bit lazy i left the coalesce in. To my surprise the damned thing started working, something to do with rounding i think, had a decimal(7,2) times 0.95 in the where clause. One stayed accurate, the other did not

Is this kind of thing documented anywhere?
Anymore of these features hiding anywhere?

Thanks

Col

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-12 : 11:18:30

Can you show us the code that's acting funny?

Teroman
Posting Yak Master

115 Posts

Posted - 2002-02-13 : 05:19:39

try this

declare @n decimal(7,2)
declare @n2 decimal(13,5)

set @n2 = 12345.67891

select isnull(@n * 0.95, @n2)
select coalesce(@n * 0.95, @n2)

gives:

------------
12345.6789

(1 row(s) affected)

---------------
12345.67891

(1 row(s) affected)

the isnull loses a dcimal place (sql serever v7 here)

bit naughty methinks

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-13 : 07:09:51

SQL Server 2000 sp 2:

declare @n decimal(7,2)
declare @n2 decimal(13,5)

set @n2 = 12345.67891

select isnull(NULL, @n2)
select isnull(@n, @n2)
select isnull(@n * 0.0, @n2)
select isnull(@n * 0.00, @n2)
select isnull(@n * 0.000, @n2)
select isnull(@n * 0.0000, @n2)
select isnull(@n * 0.00000, @n2)
select isnull(@n * 0.000000, @n2)

12345.67891
12345.68
12345.679
12345.6789
12345.67891
12345.678910
12345.6789100
12345.67891000

select coalesce(NULL, @n2)
select coalesce(@n, @n2)
select coalesce(@n * 0.0, @n2)
select coalesce(@n * 0.00, @n2)
select coalesce(@n * 0.000, @n2)
select coalesce(@n * 0.0000, @n2)
select coalesce(@n * 0.00000, @n2)
select coalesce(@n * 0.000000, @n2)

12345.67891
12345.67891
12345.67891
12345.67891
12345.67891
12345.678910
12345.6789100
12345.67891000

Edited by - Arnold Fribble on 02/13/2002 07:14:35

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-13 : 07:17:30

Another datapoint:

declare @n2 decimal(13,5)
set @n2 = 12345.67891

select isnull(NULL, @n2)
select isnull(NULL * 0.0, @n2)
select isnull(NULL * 0.00, @n2)
select isnull(NULL * 0.000, @n2)
select isnull(NULL * 0.0000, @n2)
select isnull(NULL * 0.00000, @n2)
select isnull(NULL * 0.000000, @n2)

select coalesce(NULL, @n2)
select coalesce(NULL * 0.0, @n2)
select coalesce(NULL * 0.00, @n2)
select coalesce(NULL * 0.000, @n2)
select coalesce(NULL * 0.0000, @n2)
select coalesce(NULL * 0.00000, @n2)
select coalesce(NULL * 0.000000, @n2)

12345.67891
Server: Msg 8115, Level 16, State 8, Line 6
Arithmetic overflow error converting numeric to data type numeric.
Server: Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting numeric to data type numeric.
Server: Msg 8115, Level 16, State 8, Line 8
Arithmetic overflow error converting numeric to data type numeric.
Server: Msg 8115, Level 16, State 8, Line 9
Arithmetic overflow error converting numeric to data type numeric.
Server: Msg 8115, Level 16, State 8, Line 10
Arithmetic overflow error converting numeric to data type numeric.
Server: Msg 8115, Level 16, State 8, Line 11
Arithmetic overflow error converting numeric to data type numeric.

12345.67891
12345.67891
12345.67891
12345.678910
12345.67891000
12345.6789100000
12345.678910000000

Edited by - Arnold Fribble on 02/13/2002 07:22:41

Lavos
Posting Yak Master

200 Posts

Posted - 2002-02-13 : 12:57:19

IIRC, don't the arguements for ISNULL have to be of the same type or implicitly convertible, while COALESCE can take arguements of any type? If so, this "feature" might have to do with how the engine parses the parameters since it would have to start casting things to the same type for ISNULL.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources