What’s wrong with large values of zero?

We are about to roll out an update to one of our applications and we made some schema changes to the database. A couple of floating point fields were changed to the money data type. And that’s when the fun began. QA reported the following error from one of the reports:

Arithmetic overflow error converting numeric to data type numeric.

After tracing through the stored procedure that drives the report, it boiled down to a COALESCE() function. When we used 0.00 as the 2nd parameter in COALESCE, it would throw that error under SQL Server 7. Oddly enough this only happens when the first parameter was not null.

The simple fix was to change 0.00 to 0 (no decimal point). Or use ISNULL() instead of COALESCE. This problem appears to be fixed in SQL Server 2000.

If you have access to SQL Server 7 (or MSDE 1) and you want to see this for yourself, try the following:

create table foo (id int, cur money null)

insert into foo(id, cur) values (1, null)
insert into foo(id, cur) values (2, 0.0)
insert into foo(id, cur) values (3, 4.567)

— These will work
select id, ISNULL(cur, 0.00) as c from foo
select id, coalesce(cur, 0) as c from foo

— This one will fail
select id, coalesce(cur, 0.00) as c from foo