1

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.

Steve Byfield
  • 21
  • 4
  • 2
    Find the dependencies on this table using SSMS. Perhaps there is an indexed view? And again verify that you are looking at the correct database / schema / object and that it has no INSERT triggers (you wrote it has no AFTER UPDATE trigger). – SMor Jul 17 '20 at 17:21
  • 2
    `To confirm, there is no INSTEAD OF INSERT trigger or AFTER UPDATE trigger` - what about the other types of triggers? – GSerg Jul 17 '20 at 18:43
  • Does `MyTable` have Change Tracking enabled? Perhaps the data type of new_balance has changed from `int` to `decimal(18,6)` since CT was enabled. Unlike CDC (which is a Log Reader Agent process) CT happens in the context of the user transaction. – AlwaysLearning Jul 18 '20 at 01:01
  • 3
    What about check constraints that might be using a scalar function for some sort of validation? –  Jul 18 '20 at 02:06
  • Cheers for the responses. To confirm, @GSerg - there are no triggers of any kind that have been added to the table. – Steve Byfield Jul 20 '20 at 09:02
  • @AlwaysLearning - the database does not have Change Tracking enabled. – Steve Byfield Jul 20 '20 at 09:03
  • @bbaird - there are no constraints for this particular field - there are defaults of 0.00 for other decimal(18,6) fields on the table and there is also a FK to its parent table keyed on id, seq_num. – Steve Byfield Jul 20 '20 at 09:03
  • @SMor - there are no triggers on the table and there are no views set up in the database. – Steve Byfield Jul 20 '20 at 09:05
  • Any computed column in MyTable? – Arvo Jul 20 '20 at 09:54
  • @Arvo - there are no computed columns in MyTable – Steve Byfield Jul 20 '20 at 10:00

1 Answers1

1

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.

Steve Byfield
  • 21
  • 4