Getting "Error converting data type varchar to numeric."

Nirav Gandhi

7y3.8k

"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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nirav Gandhi7y ago
Hello All,

Please don't invest time. I will convert cast function to TRY_Cast and It's works accepted.
0
Ronika Jency7y ago
Hi Gandhi,

No need to give the actual table details. our need is table definition. So that we can check the datatype.

Hope you can understand.
0
Nirav Gandhi7y ago
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.
0
Ronika Jency7y ago
Hi,

@tableData, #temp should both provide the same error. You can't insert the varchar value to int data type.

Ex:

If you declare Varchar data type column with value(test,test). You can't insert in the same column as int data type.

Table(testData)

ID(int)     column1(varchar)
1                test,test

Table(testData1)

ID(int)    column1(int)
1            can't insert the data it throws error.
0
Suraj Kumar7y ago
Request you to put the definition of table [RESPONSE_DATA_V].

Also you can attach the whole query after saving and zip as there are some minor mistakes in the query you have provided here.

Below is that one.
INSERT INTO @TableData
       SELECT  A,
              B,
              C,
              D
    FROM TableData
I think here in from you had to write #TableData but you have written TableData. If it is not temp table then it is right but put also this table definition.
0