I am experiencing this issue in SQL Server 2012 but it is also manifest in SQL Server 2017.
My database table, call it MyTable, contains a decimal(18,6) column, new_balance, which is not nullable. The table also contains two integer columns which are part of the primary key. There are no other integer fields.
A stored procedure has a parameter @new_balance which is also defined as decimal(18,6). There are also parameters for id (@id) and seq_num (@seq_num), both defined as int.
The procedure carries out an insert into the table along the lines of:
INSERT INTO MyTable (id, seq_num, new_balance)
VALUES (@id, @seq_num, @new_balance);
When @new_balance is set to 2147483647.999999 or lower, the insert proceeds as expected.
When @new_balance is set to a number greater than or equal to 2147483648, e.g. 2147483648.1, the procedure fails with an arithmetic overflow converting expression to int error. I appreciate that the max value for an int column is 2147483647.
This is also the case using the following insert:
INSERT INTO MyTable (id, seq_num, new_balance)
SELECT @id, @seq_num, @new_balance;
Given that both the parameter and table column are defined as decimal(18,6), I am struggling to see why there is a conversion to int as part of the insert (especially with the second statement where I wouldn't expect any implicit conversion).
I have also tried to cast @new_balance to decimal(18,6) explicitly as part of the INSERT statement:
INSERT INTO MyTable (id, seq_num, new_balance)
SELECT @id, @seq_num, CAST(@new_balance AS decimal(18,6));
This also didn't work.
Strangely it works fine if I specify in a query a table variable with the same definitions and carry out a similar insert:
DECLARE @MyTable TABLE (id int, seq_num int, new_balance decimal(18,6));
INSERT INTO @MyTable (id, seq_num, new_balance)
SELECT @id, @seq_num, @new_balance;
I have tried this approach in the procedure, i.e. first inserting the record into @MyTable and then trying to insert into MyTable as follows:
DECLARE @MyTable TABLE (id int, seq_num int, new_balance decimal(18,6));
INSERT INTO @MyTable (id, seq_num, new_balance) SELECT @id, @seq_num, @new_balance;
INSERT INTO MyTable (id, seq_num, new_balance) SELECT id, seq_num, new_balance FROM @MyTable;
This didn't work either.
For completeness, I also tried to create the record with a value of zero and then update the existing record - again this was unsuccessful and so this issue occurs with both an INSERT and an UPDATE:
INSERT INTO MyTable (id, seq_num, new_balance) SELECT @id, @seq_num, 0.00;
This is fine - but the next step gives the same error as reported above:
UPDATE MyTable SET new_balance = @new_balance WHERE id = @id AND seq_num = @seq_num;
To confirm, there is no INSTEAD OF INSERT trigger or AFTER UPDATE trigger on the table - there are no triggers of any sort.
Change tracking is not enabled on the database and there are no constraints for this particular field - there are default constraints on other decimal(18,6) fields that feature in the actual table.
There have also been no views added to the database.
My first question on this forum - hopefully someone will have experienced this themselves and may know how to resolve it although I couldn't find a question of a similar nature. I'm stumped.
Having been able to insert directly into the table outside of the procedure, I looked at some of the downstream events and identified the issue:
After inserting the record, the system updates the status of the associated record in a parent table. There is an AFTER UPDATE on this parent table which identifies this status change as requiring audit and runs a secondary procedure to create the audit history records. As part of this capture, the system converts the old and new values in MyTable to a user-friendly format using a scalar-valued function. As part of this, the value is split into two parts to create the necessary formatting and the whole number part was being cast as an int. By changing this cast to bigint, the issue resolved itself.
The premise of the original question was therefore wrong - the issue lay several layers down.
Thanks to those who contributed.