CODEWITHSUNDEEP
c#t-sql
user3839756
user3839756

Reputation: 813

Arithmetic overflow error converting numeric to data type numeric. For decimal less than 100.00 to NUMERIC(18,2)

I have a stored procedure that gives me an error of

Arithmetic overflow error converting numeric to data type numeric.

When I try to run it. I have traced this back to just 3 parameters that cause this. all 3 values are decimal values less than 100 (for example 36.2 or 11.92) they are of datatype decimal. in SQL the data type is NUMERIC(18, 2). They are percentage values taken from a spreadsheet. they are all ran through this method:

//Parses Percentages or returns zero if parse fails.
public static decimal PctParseOrZero(string val)
{
    decimal newVal;
    if (val.EndsWith("%"))
    {
        if (decimal.TryParse(val.Substring(0, val.Length - 1), out newVal))
        {
            return newVal;
        }
        else
        {
            return 0;
        }
    }
    else
    {
        decimal.TryParse(val, out newVal);
        return newVal;
    }
}

When I try to run my stored procedure I get the error above. Why am I getting an arithmetic overflow for values that should obviously fit into NUMERIC(18, 2)?

Upvotes: 1

Views: 2310

Answers (1)

Prisoner
Prisoner

Reputation: 1857

As you haven't show us your stored procedure, I cannot make sure what you trying to do with the decimal value.

However, please be aware that, when you retrieve the percentage value from excel, for example: 36.9%, it actually return 0.369.

Such that, in your example, even excel show the values 36.2%, but it actually passing 0.392 in your stored procedure, which violate NUMERIC(18, 2)

You can try to modify your code from:

if (decimal.TryParse(val.Substring(0, val.Length - 1), out newVal))
{
    return newVal;
}

to:

if (decimal.TryParse(val.Substring(0, val.Length - 1), out newVal))
{
    return newVal * 100;
}

Otherwise, you may need to modify your stored procedure to fit this

Upvotes: 1

Related Questions