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

Question

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

Answer 1

Hi @ahmed salah ，

Please replace with below part and check whether it is working.

SET @Con=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey ,   
IIF(ISNUMERIC(EStrat)<>1,' And Name ',' And AcceptedValuesOption_Value ') , CAST(EStrat AS NVARCHAR(2500))   
,IIF(isnull(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,'')   

I validated it from my side and it is working.

select * from dbo.AllData  

Output:

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  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.