Arithmetic overflow error converting expression to data type int

With SQL Server, let’s insert some data into a table using a stored procedure for example. Facing this SQL Server error message can be disturbing “Arithmetic overflow error converting expression to data type int”. Even if SQL conversion errors are very classical ones, finding the solution and best practices to avoid them is very useful. In my case, the stored procedure running the query is generating dynamically some SQL code. Then it executes the code and insert data from various tables into one single target table.

When running the same piece of T-SQL code independently, that is outside the stored procedure, directly into the SSMS windows, no error faced. But when integrating the same code into the stored procedure, this error is thrown:

“Msg 50000, Level 16, State 2, Procedure MyStoredProcedure, Line 123
Arithmetic overflow error converting expression to data type int.”

How to avoid the SQL Server error “Arithmetic overflow error converting expression to data type int” ?

After analysis, the error message didn’t come from the SELECT clause. The SQL INSERT clause from the same T-SQL stored procedure is throwing the error. The insertion of an integer data into the table is the root cause. For INT Data Type in SQL Server, the number must be between -2^31 (-2 to the power of 31) and 2^31 (2 to the power of 31).

Namely the range for the INTEGER type is exactly between -2,147,483,648 and 2,147,483,648.

The solution

The solution is to change the data type from INT to BIGINT or DECIMAL(11,0) for example. This solve the conversion error.

Please note that in my case the error is because the integer number is too big. It can also be because you try to insert a text value into an integer field.

Be the first to comment

Leave a Reply