Arithmetic Overflow error converting varchar to numeric when using IsNumeric in SQL

Question

When I try to insert into a temporary table a decimal, I get an arithmetic overflow error:

Arithmetic overflow error converting varchar to data type numeric.

This is the SQL:

CREATE TABLE #ObDiabetesEncounterIDs
(
     PatientEncounterID int, 
     Value decimal(5,2)
)

INSERT INTO #ObDiabetesEncounterIDs(PatientEncounterID, Value)
    SELECT PatientEncounterID, Value 
    FROM Observation 
    WHERE Term = 'HGBA1C' 
      AND ISNUMERIC(Value) = 1 
      AND Value IS NOT NULL 
      AND PatientEncounterID IS NOT NULL;

Most of the decimal values are between 5.1 to 15.1. There are a few values that are whole numbers like 15 and 10. There are 2 that are out of that range such as 1405 and 151. I thought I accommodated these values by using the data type decimal(5,2).

Where am I going wrong?

UPDATE Updating the precision of the decimal helped. This accounted for the 1 record that was 1403. But there is also 1 record that has a comma instead of a period. So, the value is 9,7. So, I believe I will need to check for ISNUMERIC then change any commas to periods, then cast as a decimal.

DECIMAL(5,2) has 5 digits overall, of which 2 are after the decimal point - so you can store values from 0.00 to 999.99 in that type - a value like 1405 CANNOT be stored in such a decimal column! — marc_s

STLDev STLDev · Accepted Answer · 2017-01-27T04:17:43

Since Value is character data, cast it to DECIMAL in your SELECT statement:

CREATE TABLE #ObDiabetesEncounterIDs(PatientEncounterID int, Value decimal(7,2));

INSERT INTO #ObDiabetesEncounterIDs(PatientEncounterID, Value)
SELECT PatientEncounterID, CAST(Value AS DECIMAL(7,2))
  FROM Observation 
  WHERE Term = 'HGBA1C' 
    AND Value IS NOT NULL 
    AND PatientEncounterID IS NOT NULL;

To test this, I did the following (Note that in the following example I named the source table MyObservation instead of Observation so as not to interfere with the original table):

CREATE TABLE [dbo].[MyObservation](
    [PatientEncounterID] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](50) NULL,
    [Term] [varchar](50) NULL,
    CONSTRAINT [PK_MyObservation] PRIMARY KEY CLUSTERED (
        [PatientEncounterID] ASC
    )WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[MyObservation]
    (Value, Term)
    Values ('.01', 'HGBA1C')
        ,('.12', 'HGBA1C')
        ,('1.23', 'HGBA1C')
        ,('12.34', 'HGBA1C')
        ,('123.45', 'HGBA1C')
        ,('1234.56', NULL)
        ,('12345.67', 'HGBA1C')
        ,('non-numeric', 'HGBA1C')
        ,('9.98', 'Not HGBA1C')
        ,('123.4958', 'HGBA1C')

CREATE TABLE #ObDiabetesEncounterIDs(
    PatientEncounterID int, Value decimal(7,2)
);

INSERT INTO #ObDiabetesEncounterIDs(PatientEncounterID, Value)
SELECT PatientEncounterID, CAST(Value AS DECIMAL(7,2))
    FROM [dbo].[MyObservation]
    WHERE Term = 'HGBA1C' 
    AND ISNUMERIC(Value) = 1
    AND Value IS NOT NULL 
    AND PatientEncounterID IS NOT NULL;

SELECT * FROM #ObDiabetesEncounterIDs

The above worked flawlessly on my SQL Server 2014 system.

Arithmetic Overflow error converting varchar to numeric when using IsNumeric in SQL

2 Answers