SQLTeam.com | Weblogs | Forums

Conversion failed when converting varchar value 'Manual reset' to data type int?

I working on sql server 2012 i face error

conversion failed when converting varchar value 'Manual reset' to data type int

error done on script below

to solve issue

instead of

AcceptedValuesOption_Value ='Manual Reset'
use
Name ='Manual Reset'
so How to modify statment below to accept name instead of AcceptedValuesOption_Value
I use AcceptedValuesOption_Value for numbers only
and Name for text

create table dbo.condition
 (
 TradeCodeControlID int,
 VersionYear int,
 [Version] int,    
 CodeTypeID int,
 RevisionID int,    
 Code nvarchar(200),
 ZPLID int,
 ZfeatureKey bigint,
 ZfeatureType int,
 EStrat nvarchar(100),
 EEnd nvarchar(20)
 )
    
 insert into dbo.condition(TradeCodeControlID,VersionYear,[Version],CodeTypeID,RevisionID,Code,ZPLID,ZfeatureKey,ZfeatureType,EStrat,EEnd)
 values
 (8122,2020,26,849774,307683692,8536509065,363712,1509900011,NULL,N'=''Manual Reset''',NULL)
 --update dbo.condition set eend=''
 CREATE TABLE [dbo].[PartAttributes](
     [PartID] [int] NOT NULL,
     [ZfeatureKey] [bigint] NULL,
     [AcceptedValuesOption_Value] int  NULL,
     [Name] [nvarchar](500) NOT NULL
 ) ON [PRIMARY]
 insert into [dbo].[PartAttributes](PartID,ZfeatureKey,AcceptedValuesOption_Value,Name)
 values
 (7368955,1509900011,NULL,'Manual Reset'),
 (7368956,1509900011,NULL,'Manual Reset'),
 (7368957,1509900011,NULL,'Manual Reset'),
 (7368958,1509900011,NULL,'Manual Reset'),
 (7368959,1509900011,NULL,'Manual Reset'),
 (3733735,1509900011,80,'80°C'),
 (3733736,1509900011,80,'80°C'),
 (3733737,1509900011,80,'80°C'),
 (3733738,1509900011,80,'80°C'),
 (3733739,1509900011,80,'80°C')
    
 CREATE TABLE [dbo].[Allfeatures](
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [FeatType] [int] NULL,
     [AcceptedValueID] [int] NULL,
     [IsNumericValues] [int] NULL
 ) ON [PRIMARY]
 insert into [dbo].[Allfeatures](ZPLID,ZfeatureKey,FeatType,AcceptedValueID,IsNumericValues)
 values
 (363712,NULL,0,0,0),
 (363712,1509900011,2044,2370,1)
    
 CREATE TABLE dbo.AllData
         (
    
         PartID INT,    
         Code VARCHAR(20),    
         CodeTypeID INT,    
         RevisionID BIGINT,    
         ZPLID INT,    
         ConCount INT
         )
    
    
         DECLARE @Sql nvarchar(max)
         DECLARE @Con nvarchar(max)
         DECLARE @ConStr nvarchar(max)
                 SET @Con=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')')   
                 FROM dbo.Condition CC  INNER JOIN dbo.Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues >0
                 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
                
                
    
    
         SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' +  case when PATINDEX('%[><=]%', EStrat)>0 then 
    'AcceptedValuesOption_Value ' 
    when EStrat is null then
    'Name '
    else 
    'Name '
    end  ) , case when PATINDEX('%[><=]%', EStrat)>0 then
 CAST(EStrat AS NVARCHAR(2500))
 when EStrat is null then
 ' is null '
 else
 ''+CAST(EStrat AS NVARCHAR(2500)) +''
 end ,')')   --ValueName
                 FROM dbo.condition CC INNER JOIN [dbo].[Allfeatures] AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
    
                
                 SET @ConStr = IIF(LEN(@Con)>3 AND LEN(@ConStr)>3 , CONCAT('Or ',@ConStr),@ConStr )
                    
            
    
                 SET @Sql= CONCAT('INSERT INTO dbo.AllData',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
                 FROM 
                 dbo.PartAttributes PM 
                 INNER JOIN    dbo.Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',                
                 'Where (1=1 and  ',@Con ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                 ' Having Count(1)>= ',(SELECT COUNT(1) FROM dbo.Condition))
    
    
                 -- print @SQL
             EXEC (@SQL)

expected result

PartID Code CodeTypeID RevisionID ZPLID ConCount
7368955 8536509065 849774 307683692 363712 1
7368956 8536509065 849774 307683692 363712 1
7368957 8536509065 849774 307683692 363712 1
7368958 8536509065 849774 307683692 363712 1
7368959 8536509065 849774 307683692 363712 1

What have you tried? and by the way your script has errors, please try it first before posting