Arithmetic overflow error converting expression to data type int. (in two places)

Question

I'm getting this error

Msg 8115, Level 16, State 2, Line 102 Arithmetic overflow error converting expression to data type int. The statement has been terminated.

Msg 8115, Level 16, State 2, Line 114 Arithmetic overflow error converting expression to data type int. The statement has been terminated.

CREATE TABLE city(
 citycode int,
 city_name varchar(78)
 CONSTRAINT cityPK primary key(citycode)
 )

 CREATE TABLE workers(
 work_no int,
 NameW varchar(90),
 LastName varchar(30),
 ID int,
 phone varchar(50),
 citycode int,
 CONSTRAINT workerPK primary key(work_no),
 CONSTRAINT workerFK foreign key(citycode)
 REFERENCES city(citycode)
 )
 CREATE TABLE managers(
 manager_no int,
 Manager_name varchar(90),
 Manager_last varchar(90),
 city_code int,
 CONSTRAINT managerPK primary key(manager_no),
 CONSTRAINT managerFK1 foreign key(city_code)
 REFERENCES city(citycode)
 )

 CREATE TABLE mahlakot(
 no int,
 Mahlka_name varchar(50),
 Mahlka_type varchar(50),
 Manager_no int,
 CONSTRAINT mahlakotPK primary key(no),
 CONSTRAINT mahlakotFK1 foreign key(Manager_no)
 REFERENCES managers(manager_no)
 )

 CREATE TABLE products(
 Product_ID int,
 Prod_name varchar(89),
 Mahlka_no int,
 Prise money,
 CONSTRAINT productsPK PRIMARY KEY(Product_ID),
 CONSTRAINT productFK1 foreign key(Mahlka_no)
 REFERENCES mahlakot(no)
 )

 CREATE TABLE Coustmers(
 Cust_id int,
 Cust_name varchar(80),
 Cust_last varchar(80),
 Cust_date date,
 saled_by int,
 product_id int,
 CONSTRAINT CUSTPK PRIMARY KEY(Cust_id),
 CONSTRAINT CUSTFK1 FOREIGN KEY(saled_by)
 REFERENCES workers(work_no),
 CONSTRAINT CUSTFK2 FOREIGN KEY(product_id)
 REFERENCES products(Product_ID)
 )

 insert into city VALUES
 (800,'Haifa'),
 (802,'Akko'),
 (804,'Tel-Aviv'),
 (805,'Tamra'),
 (806,'Nahriya'),
 (808,'Qiryat-yam'),
 (810,'Tal Al')

 insert into workers VALUES
(1234,'Ran','Synko',443987871,'052-9890987',800),
(1567,'Ahmad','Amir',66434523,'052-9890987',802),
(5532,'Samir','Nizar',23623626,'052-9890987',805),
(7786,'Nizar','Jakov',53535312,'052-9890987',802),
(9099,'Ali','Yousef',12515161,'052-9890987',808),
(1141,'Adon','Hmado',12616335,'052-9890987',805),
(8865,'Yaan','lev',724723623,'052-9890987',802),
(9991,'Moaid','Ronaldo',734734734,'052-9890987',802),
(9921,'Alex','Barlev',443944471,'052-9890987',810)

insert into managers VALUES
(90001,'khaled','ayman',804),
(90002,'Ayman','Dada',802),
(90004,'Saleh','Amir',810),
(90005,'Yiki','Friki',810),
(90006,'Nano','Rano',800),
(90100,'Pizza','Bolos',808),
(90101,'Homos','Ninar',804)

insert into mahlakot  VALUES
(100,'SmartPhones','phone',90004),
(102,'Derit Sims','phone',90004),
(103,'Computers','devices',90001),
(104,'PartsComps','devices',90001),
(105,'Tvs&video','TV',90101),
(106,'SchoolGlasser','pupils services',90002),
(107,'Electra Devs','Electronic',90005),
(108,'DataCenter Webs','Website',90006),
(109,'Other','.)..',90100)

insert into products VALUES
(1002003004001,'LG SmartPhone',100,1500),
(1002003004002,'iPhone Apple x',100,890),
(1012003004005,'WatchSoirt Apple x',100,2300),
(1022322000012,'Toshiba Laptop 500gb',103,1567),
(1020202030304,'Motherboard Gigabyte',104,900),
(1030405060707,'Panosonic Tele Plasma',105,560),
(1055998988989,'Panosara Video Cast',105,230),
(1055040400404,'DataCer Arduino U',107,100),
(5884949598595,'HardDisk SSD 600G',104,800)


insert into Coustmers Values
(10,'Ahmad','Ayoub','2017-06-06',9099,1055998988989),
(11,'Sys','Hmado','2017-06-06',1141,1002003004001),
(12,'Hram','Sleman','2017-06-06',1234,1002003004002),
(13,'Moustafa','AlAmir','2017-06-06',9921,5884949598595),
(14,'Mariam','AlAmir','2017-06-06',9099,1022322000012),
(15,'Daha','Wlilazasja','2017-06-06',9099,1012003004005),
(16,'Yakov','Anjilika','2017-06-06',7786,1055998988989),
(17,'itsik','diri','2017-06-06',9099,1002003004001),
(18,'firas','diri','2017-06-06',1141,1002003004001)

with this SQL query

SqlZim · Accepted Answer · 2017-06-16 18:11:21Z

2

Change Product_Id to bigint to support values that size.

Rextester demo with corrected code: http://rextester.com/IQE74281

To support numbers like: 1002003004001 it would be best to use bigint or decimal/numeric.

+-----------+--------------------------------------------------------------------------+---------+
| Data type |                                  Range                                   | Storage |
+-----------+--------------------------------------------------------------------------+---------+
| bigint    | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | 8 Bytes |
| int       | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)                         | 4 Bytes |
| smallint  | -2^15 (-32,768) to 2^15-1 (32,767)                                       | 2 Bytes |
| tinyint   | 0 to 255                                                                 | 1 Byte  |
+-----------+--------------------------------------------------------------------------+---------+

Reference:

answered Jun 16, 2017 at 18:11

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

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

1 Comment

Nikita Over a year ago

I have error when cast(a.Zip as nvarchar(10) and fix it by cast(cast(a.Zip as bigint) as nvarchar(10))

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to data type int. (in two places)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related