-1

I have a .NET app that sends a table-valued parameter to a SQL Server stored procedure.

In the SQL Server table, the column is defined as DECIMAL(20,0).

Table-valued column on .NET defined as

Columns.Add(new SqlMetaData(name, SqlDbType.Decimal));

When sending a 20 or 19 digit long .net decimal value like this Convert.ToDecimal(81210694789319013214), SQL Server throws an exception:

SqlTruncateException: Numeric arithmetic causes truncation

On a 18 digits long decimal, it works just fine.

There's not much documentation about this error online.

Thanks

Nir-Z
  • 819
  • 1
  • 13
  • 31
  • 2
    Can't repro - works just fine, both in SSMS and from a .NET 4.8.1 / ADO.NET code snippet. You need to **show us** just **HOW** you do the insert operation! What does your stored procedure look like? How are you calling it from C#? – marc_s Mar 26 '23 at 10:27
  • This question is not answerable without relevant code – Charlieface Mar 26 '23 at 12:48
  • found the issue. On table valued parameter had to write like this      Columns.Add(new SqlMetaData(name, SqlDbType.Decimal, 20, 0)); instead of Columns.Add(new SqlMetaData(name, SqlDbType.Decimal)); – Nir-Z Mar 27 '23 at 09:34

2 Answers2

0

You haven't showed us any code - so this is purely speculative. But just maybe, check your stored procedure parameter for the decimal value - how is it defined?

Did you just use DECIMAL without providing any precision and scale values?

If so: DECIMAL defaults to DECIMAL(18,0) - which would explain why it works with 18 digit numbers, but not with 20 digits.

You need to explicitly define the precision and scale to use - Best Practice is to do this ALWAYS anyway!

CREATE PROCEDURE dbo.InsertDecimal
    @InputDecimal DECIMAL(20,0)     -- define 20 digits, 0 after decimal point
AS
  .....

With this - everything should work just fine ....

The official Microsoft documentation on DECIMAL will tell you more details

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • As I mentioned on DB the type is already DECIMAL(20,0) but from .NET app value is sent as Convert.ToDecimal(field.Value) – Nir-Z Mar 26 '23 at 11:12
  • In your C# code, you should use a **properly parametrized** query, and you should use a `SqlParameter` - something like this: `cmdYourSqlCommand.Parameters.Add("@InputDecimal", SqlDbType.Decimal, 20).Value = (your value here)` - then everything works fine - don't use strings and convert and stuff like that .... – marc_s Mar 26 '23 at 16:07
0

If you are using EF

Set DecimalPropertyConfiguration.HasPrecision Method as per the link Decimal precision and scale in EF Code First

Girish
  • 1
  • 2