Getting "Error converting data type varchar to numeric

Question

Post reply

Getting "Error converting data type varchar to numeric

nirav.gandhi7

SSC-Addicted

Points: 415

May 31, 2019 at 11:50 am

#3648933

"Getting "Error converting data type varchar to numeric." in Table variable(@TableData) but not in #Temp"

I am facing issues while inserting data on table variable. I have created two table with same datatype,

1)@TableData

2)#TableData

Now I am inserting same data on both tables, but I am getting error “Error converting data type varchar to numeric.” in @tableData but not in #temp. Please check below example.

DROP TABLE IF EXISTS #temp  
DROP TABLE IF EXISTS #TableData  
  
Declare @TableData as Table  
(   ID int Primary key Identity(1,1),  
    A [int] NULL,  
    B [varchar](30) NULL,  
    C [int] NULL,  
    D [int] NULL  
)  
  
Create table #TableData   
(   ID int Primary key Identity(1,1),  
    A [int] NULL,  
    B [varchar](30) NULL,  
    C [int] NULL,  
    D [int] NULL  
)  
  
INSERT INTO @TableData  
       SELECT  A,   
              B,   
              C,   
              D  
    FROM TableData   
       
CREATE TABLE #temp  
(ID VARCHAR(50),   
 A     VARCHAR(5000),   
 B          VARCHAR(5000)  
)  
  
  
INSERT INTO #temp (  
    ID  
    ,A  
    ,B  
    )  
SELECT ID  
    ,SUM(A) AS AR_USERENTRY  
    ,AR_AREA  
 FROM (  
SELECT DISTINCT   
       EP.ID,   
       SUM(ISNULL(CAST(A AS NUMERIC(18, 0)), 0)) AR_USERENTRY,  
     --  AR_USERENTRY   
       B  
FROM @TableData AS T  
inner join [RESPONSE_DATA_V] AS V on    T.ID=V.ID  
       
WHERE [AR_ISDELETED] = 0  
      AND AR_USERENTRY != '$'  
      AND AR_USERENTRY != '+'  
      AND AR_USERENTRY != ','  
      AND AR_USERENTRY != '-'  
      AND AR_USERENTRY != '.'  
      AND AR_USERENTRY != '\'     
      AND 1 = CASE   
        WHEN ISNUMERIC(AR_USERENTRY) = 1  
            AND CAST(AR_USERENTRY AS NUMERIC(18, 2)) >= 0  
            THEN 1  
        ELSE 0  
        END --M5     
  
GROUP BY EP.A,   
         B  
            ) TT   
            WHERE B IS NOT NULL  
         GROUP BY A  
            ,B

Capture

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

At first glance and without actually looking at the data looks like this is the problem - CAST(AR_USERENTRY AS NUMERIC(18, 2)) >= 0 You can give TRY_CAST a try - as that will not fail the script if the actual value is non numeric

Then again you would not really need to check both ISNUMERIC(AR_USERENTRY) = 1

AND CAST(AR_USERENTRY AS NUMERIC(18, 2)) >= 0

Thanks

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 2

We can't see all the tables you're using here, so guessing, but at the end, you both check if a column is numeric and then try to convert it to numeric. If it's already a numeric, then why convert?

WHEN ISNUMERIC(AR_USERENTRY) = 1

AND CAST(AR_USERENTRY AS NUMERIC(18, 2)) >= 0

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

nirav.gandhi7 SSC-Addicted Points: 415 More actions · Answer 3

Hello All,

I have added a dummy query. I can't give actual table details. but the error was having on Table variable but not in # table.

Above code is just an example.please do not copy and run on SQL server.