SQL Server: arithmetic overflow error converting expression to data type int in where condition

Question

I have this query executing in client side

select * 
from orders 
where orderid > @id and orderid < @id + 1000

Now the @id value is 2147483647 and I'm getting this error:

I tried to cast and convert like below

select * 
from orders 
where orderid > @id and orderid < cast(@id + 1000 as bigint)

but no luck.

Is there a way to cast/convert the addition into bigint in the where condition?

Thanks

Apparently you need to change the data type of the variable @id to bigint. But I think that you also can do like this cast(@id as bigint) + 1000 — Eliseu Marcos
– Eliseu Marcos, Commented Mar 30, 2021 at 14:59

Gordon Linoff · Accepted Answer · 2021-03-30 14:58:09Z

3

Cast before adding:

select *
from orders
where orderid > @id and orderid < cast(@id as bigint) + 1000 ;

Or better yet, declare @id to be a bigint.

answered Mar 30, 2021 at 14:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1