sql server programming: Artihmetic overflow error -- sql server programming

Re: Artihmetic overflow error

Steve Kass
11/6/2003 8:52:16 PM

Gary,

I believe this is an undersirable behavior due to the fact that
SQL Server has no strict rules on data type precedence for the
hundreds of different numeric types. Not long ago I reported
a similar situation to Microsoft, but I don't know if it's been
classified as a bug or not.

Here is a small repro:

create table mytable(
id numeric (18,0) not null
)
insert into mytable (id) values (0)
go

-- works
declare @sid numeric(20,0)
set @sid = 999999999999990002
select @sid = min(id) from mytable where id > @sid + 9999
select @sid
go

alter table mytable add constraint pk_mytable unique (id)
go

-- fails
declare @sid numeric(20,0)
set @sid = 999999999999990002
select @sid = min(id) from mytable where id > @sid + 9999
select @sid
go

drop table mytable

Unfortunately, when two different numeric types are being compared,
there's no consistency on what conversion takes place, and here, it
sadly depends on whether there is an index or not on the table. A
workaround is this, and I hope some variation on this idea will work
for you.

declare @sid numeric(20,0)
set @sid = 999999999999990002
select @sid = min(id) from mytable
where id >
case when @sid <= 999999999999990000
then cast(@sid as decimal(18,0)) + 9999
else 999999999999999999 end
select @sid
go

That way you never compare against a different decimal type.

[quoted text, click to view]

Artihmetic overflow error

Gary
11/6/2003 11:07:32 PM

I'm trying execute some sql statements in a loop. The table I'm operating on
is very large, so I'm updating the table in batches of 10000 rows at a time.

I get an error after processing for a while:

Arithmetic overflow error converting numeric to data type numeric.Query
Analyzer points to this line:

select @sid = min(ID) from MyTable where ID > @sid + 9999

which is in the following loop:

while (@sid is not null)
begin
..
..

select @sid = min(ID) from MyTable where ID > @sid + 9999
..
..
end

@sid is declared as NUMERIC(20,0).
ID is declared as NUMERIC(18,0).

I don't see why I am getting this error. Any advice is appreciated.

Thanks.

-Gary

Re: Artihmetic overflow error

Gary
11/6/2003 11:49:22 PM

CREATE TABLE MyTable(ID NUMERIC (18,0) NOT NULL, WASSET INT NOT NULL DEFAULT
0, ASSET INT, REQTIME DATETIME NULL, COMPLTIME DATETIME NULL)

INSERT INTO MyTable (ID, WASSET, ASSET, REQTIME, COMPLTIME)
VALUES( 999999999998391300, 750, 337122893, '2002-02-03
19:59:23.000','2002-02-03 19:59:39.000')

Thanks again for any advice.

-Gary

[quoted text, click to view]

Re: Artihmetic overflow error

Gary
11/8/2003 12:13:29 AM

Thanks! This was enough of a hint for me to get past the error.

-Gary

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.