Error converting data type varchar to numeric while inserting/ updating

Question

I was trying to insert/update records in bulk using merge statement. But I am getting following error. I have searched online i couldn't find a way to fix. Can some one tell me where I am doing wrong?

Error converting data type varchar to numeric while inserting'

Table structure

 CREATE TABLE [Metric].[MetricGoal]
 (
    [metricGoalId] [int] IDENTITY(1,1) NOT NULL,
    [profileConfigId] [int] NOT NULL,
    [metricGoalName] [varchar](200) NULL,
    [metricIndicatorId] [int] NOT NULL,
    [marketConfigId] [int] NOT NULL,
    [regionConfigId] [int] NOT NULL,
    [goalYearConfigId] [int] NOT NULL,
    [goalPeriodConfigId] [int] NOT NULL,
    [targetValue] [decimal](20, 3) NULL,
    [actualValue] [decimal](20, 3) NULL,
    [metricGoalStatusConfigId] [int] NOT NULL,
    [metricGoalStatusReasonConfigId] [int] NOT NULL,
    [ownerId] [int] NULL,
    [workerId] [int] NULL,
    [createdOn] [datetime] NOT NULL,
    [createdBy] [int] NOT NULL,
    [updatedOn] [datetime] NOT NULL,
    [updatedBy] [int] NOT NULL,
    [lineOfBusinessConfigId] [int] NULL,
    [productConfigId] [int] NULL,
    [serviceAreaConfigId] [int] NULL,
  )

User Defined Table Type(created type with only columns that needs to insert / update):

CREATE TYPE [Metric].[MetricGoalType3] AS TABLE
(
    [metricGoalId] [int] NULL,
    [lineOfBusinessConfigId] [int] NULL,    
    [metricIndicatorId] [int] NOT NULL,
    [goalYearConfigId] [int] NOT NULL,
    [goalPeriodConfigId] [int] NOT NULL,
    [marketConfigId] [int] NOT NULL,
    [targetValue] [decimal](20, 3) NULL,
    [actualValue] [decimal](20, 3) NULL,
    [metricGoalStatusConfigId] [int] NOT NULL,
    [metricGoalStatusReasonConfigId] [int] NOT NULL,
    [ownerId] [int] NULL,
    [workerId] [int] NULL
)

Stored procedure to insert/update using Merge:

CREATE PROCEDURE [Metric].[prMaintainMetricGoalBulkLoad]
    @currUserId INT = NULL,
    @currProfileConfigId INT = NULL,
    @tblMetricGoal [Metric].[MetricGoalType3] READONLY
AS
    DECLARE @now DATETIME = GETDATE()
BEGIN
    SET NOCOUNT ON;

    MERGE INTO [Metric].[MetricGoal] T
    USING @tblMetricGoal S ON (T.metricGoalId = S.metricGoalId)

    WHEN MATCHED 
       THEN UPDATE
            SET T.targetValue = CASE WHEN S.targetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),T.targetValue) END, 
                T.actualValue = CASE WHEN S.actualValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),T.actualValue) END,  
                T.metricGoalStatusConfigId = CASE WHEN S.metricGoalStatusConfigId =  -1 THEN NULL ELSE ISNULL(S.metricGoalStatusConfigId,T.metricGoalStatusConfigId) END, 
                T.metricGoalStatusReasonConfigId = CASE WHEN S.metricGoalStatusReasonConfigId = -1 THEN NULL ELSE ISNULL(S.metricGoalStatusReasonConfigId,T.metricGoalStatusReasonConfigId) END, 
                T.ownerId = CASE WHEN S.ownerId = -1 THEN NULL ELSE ISNULL(S.ownerId,T.ownerId) END, 
                T.workerId = CASE WHEN S.workerId = -1 THEN NULL ELSE ISNULL(S.workerId,T.workerId) END, 
                T.updatedOn = @now,
                T.updatedBy = @currUserId

WHEN NOT MATCHED BY TARGET
    THEN INSERT (profileConfigId,
                 --metricGoalName,
                 metricIndicatorId, lineOfBusinessConfigId, marketConfigId,
                 --productConfigId,
                 --serviceAreaConfigId,
                 --regionConfigId,
                 goalYearConfigId, goalPeriodConfigId, targetValue, actualValue,
                 metricGoalStatusConfigId, metricGoalStatusReasonConfigId,
                 ownerId, workerId, createdOn,  createdBy, 
                 updatedOn, updatedBy)
         VALUES (@currProfileConfigId,
                 --S.metricGoalName,
                 S.metricIndicatorId, S.lineOfBusinessConfigId, S.marketConfigId,
                 --NULLIF(S.productConfigId,-1),
                 --NULLIF(S.serviceAreaConfigId,-1),
                 --S.regionConfigId,
                 S.goalYearConfigId, S.goalPeriodConfigId,
                 CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),
                 CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),
                 S.metricGoalStatusConfigId, S.metricGoalStatusReasonConfigId,
                 NULLIF(S.ownerId, -1),
                 NULLIF(S.workerId, -1),
                 @now, @currUserId, @now, @currUserId);
END

Execution (used SQL Server Profiler to get below statements)

declare @p3 Metric.MetricGoalType3
insert into @p3 values(820,819,4,602,570,694,39.000,43.000,655,660,1585,NULL)
insert into @p3 values(NULL,819,4,602,570,1853,NULL,NULL,655,660,NULL,NULL)

exec Metric.prMaintainMetricGoalBulkLoad @currUserId=1618,@currProfileConfigId=301,@tblMetricGoal=@p3

use try_convert or try_cast instead of convert or cast — Paul Maxwell
– Paul Maxwell, Commented Nov 28, 2017 at 1:32

sepupic · Accepted Answer · 2017-11-28 15:42:30Z

3

These 2 rows contain error:

T.targetValue = CASE WHEN S.targetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),T.targetValue) END, 
T.actualValue = CASE WHEN S.actualValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),T.actualValue) END,

Both targetValue and actualValue are decimal(20,3), so how can you use '' that is string with decimals?

It should be

T.targetValue = CASE WHEN S.targetValue is null THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,null)),T.targetValue) END, 
T.actualValue = CASE WHEN S.actualValue is null THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,null)),T.actualValue) END,

even it has no sense but at least there vill be no conversion from varchar to numeric.

Here is how to reproduce your error (I'll use variables instead of tables):

declare @StargetValue DECIMAL(20,3) = 10, @TtargetValue DECIMAL(20,3) = 20;
set @ttargetValue = CASE WHEN @StargetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(@StargetValue,'')),@TtargetValue) END

When you try to confront your decimal with '' you get the error because '' just cannot be converted to decimal.

answered Nov 28, 2017 at 15:42

sepupic

8,7371 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

crony Over a year ago

You are awesome. I did find where I am getting error but didn't find a way to fix it and it is now. Thanks to you

Paul Maxwell · Accepted Answer · 2017-11-28 01:40:00Z

0

There is data that will not convert correctly, exactly what or where I can't tell but since you indicate you are using SQL Server 2012 then use TRY_CONVERT or TRY_CAST instead of convert or cast.

These 2 newer functions do not cause a failure if the data cannot be converted, they simply return NULL instead. While this may not solve bad data it does help. For example you can use these in a where clause e.g.

 select * 
 from to_be_imported
 where try_convert(date,stringcolumn) IS NULL

answered Nov 28, 2017 at 1:40

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

1 Comment

Paul Maxwell Over a year ago

"I have tried"... what? Have you tried to isolate the problem(s) using try_cast in a where clause? I hope you realize all I can do is supply you with tools, I'm afraid you have to do the hard yards with the data

Russ · Accepted Answer · 2017-11-28 02:03:20Z

0

It looks like the insert portion of the statement does not align with your table definition. The third field in your insert list is lineOfBusinessConfigId which is trying to insert into the third field of the table, a varchar column metricGoalName.

answered Nov 28, 2017 at 2:03

Russ

1

Collectives™ on Stack Overflow

Error converting data type varchar to numeric while inserting/ updating

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related