Getting "Error converting data type varchar to numeric
-
"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
-
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
-
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 -
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply