Error Conversion failed when converting the varchar value 'Solid State Relay' to data type int.

Question

I Work ON SQL SERVER 2012 I face issue and I can't solve it

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Solid State Relay' to data type int.

if object_id(N'tempdb..#PartAttributes') is not null drop table #PartAttributes  
      
      
 if object_id(N'tempdb..#Condition') is not null drop table #Condition  
      
      
 if object_id(N'tempdb..#Allfeatures') is not null drop table #Allfeatures  
 if object_id(N'tempdb..#Codes') is not null drop table #Codes  
      
 create table #Allfeatures  
    (  
     ZPLID INT,  
     ZFeatureKey nvarchar(20),  
     IsNumericValue int  
    )   
    insert into #Allfeatures(ZPLID,ZFeatureKey,IsNumericValue)  
    values(75533,'1505730036',0)  
                              
    create table #Condition  
    (  
     Code nvarchar(20),  
     ZFeatureKey nvarchar(20),  
     ZfeatureType nvarchar(20),  
     EStrat  nvarchar(50),  
     EEnd NVARCHAR(50)  
    )  
    insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)  
    values  
    ('8535400000','1505730036',NULL,'>1000',' '),  
    ('8535400000','1505730036',NULL,'<>''Solid State Relay''',' '),  
    ('8535400000','1505730036',NULL,'=''N/A''',' '),  
    ('8535400000','1505730036',NULL,NULL,' ')  
                              
    CREATE TABLE #PartAttributes  
    (  
     PartID INT,  
     ZFeaturekEY NVARCHAR(20),  
     AcceptedValuesOption_Value  INT,  
     Name nvarchar(20)  
    )  
    insert into #PartAttributes(PartID,ZFeaturekEY,AcceptedValuesOption_Value,Name)  
    values  
    (4977941,1505730036,280,'1.2kV'),  
    (4977936,1505730036,280,'280VDC'),  
    (4977935,1505730036,280,'100V'),  
    (4977808,1505730036,280,'N/A'),  
    (4979054,1505730036,280,'24VAC/DC')  
                              
     DECLARE @Sql nvarchar(max)  
     DECLARE @ConStr nvarchar(max)  
                                              
    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 ,')')     
 FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue =0  
 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')  
  
  
  
              
     ----------------                      
     SET @Sql= CONCAT(' SELECT PartID, Code, Count(1) as ConCount  
     FROM #PartAttributes PM   
     INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',  
     'Where 1=1 and (', @ConStr, ' ) Group By PartID,Code ' ,  
     ' Having Count(1)> = ',(SELECT COUNT(1) FROM #Condition))  
        
  --print @SQL    
     EXEC (@SQL)  

ExpectedResult

PartID Code ConCount  
4977808 8535400000 4  
4977935 8535400000 4  
4977936 8535400000 4  
4977941 8535400000 4  
4979054 8535400000 4  

Answer 1

Hi @ahmed salah ,

Please refer below:

   DECLARE @Sql nvarchar(max)  
      DECLARE @ConStr nvarchar(max)  
                                                  
     SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey ,   
	 IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' +  case when PATINDEX('%[><=]%', EStrat)>0  then   
    case when PATINDEX('%[0-9]%', EStrat)>0 then   
	'AcceptedValuesOption_Value '   
	else   
	'cast(AcceptedValuesOption_Value as varchar(100)) '  end   
    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 ,')')     
  FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue =0  
  FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')  
             
      ----------------                      
      SET @Sql= CONCAT(' SELECT PartID, Code, Count(1) as ConCount  
      FROM #PartAttributes PM   
      INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',  
      'Where 1=1 and (', @ConStr, ' ) Group By PartID,Code ' ,  
      ' Having Count(1)> = ',(SELECT COUNT(1) FROM #Condition))  
            
   --print @SQL    
      EXEC (@SQL)  

Output:

PartID	Code	ConCount  
4977808	8535400000	4  
4977935	8535400000	4  
4977936	8535400000	4  
4977941	8535400000	4  
4979054	8535400000	4  

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.

Answer 2

Hi @ahmed salah ,

I used one function to remove alphabets from one string as below:

CREATE FUNCTION dbo.udf_RemoveAlphabets  
(@str NVARCHAR(256))  
RETURNS VARCHAR(256)  
as   
begin  
DECLARE @lpcounter INT;  
SET @lpcounter = 0;  
WHILE @lpcounter <= 26  
    BEGIN  
        SET @str = REPLACE(@str, CHAR(65+@lpcounter), '');  
        SET @lpcounter  = @lpcounter + 1;  
    END;  
RETURN @str;  
end  

Then call this function as below:

if object_id(N'tempdb..#PartAttributes') is not null drop table #PartAttributes       
if object_id(N'tempdb..#Condition') is not null drop table #Condition   
if object_id(N'tempdb..#Allfeatures') is not null drop table #Allfeatures  
if object_id(N'tempdb..#Codes') is not null drop table #Codes  
          
create table #Allfeatures  
(  
ZPLID INT,  
ZFeatureKey nvarchar(20),  
IsNumericValue int  
)   
insert into #Allfeatures(ZPLID,ZFeatureKey,IsNumericValue)  
values(75533,'1505730036',0)  
                                  
create table #Condition  
(  
Code nvarchar(20),  
ZFeatureKey nvarchar(20),  
ZfeatureType nvarchar(20),  
EStrat  nvarchar(50),  
EEnd NVARCHAR(50)  
)  
insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)  
values  
('8535400000','1505730036',NULL,'>1000abc',' '),  
('8535400000','1505730036',NULL,'<>''Solid State Relay''',' '),  
('8535400000','1505730036',NULL,'=''N/A''',' '),  
('8535400000','1505730036',NULL,NULL,' ')  
                                  
CREATE TABLE #PartAttributes  
(  
PartID INT,  
ZFeaturekEY NVARCHAR(20),  
AcceptedValuesOption_Value  INT,  
Name nvarchar(20)  
)  
insert into #PartAttributes(PartID,ZFeaturekEY,AcceptedValuesOption_Value,Name)  
values  
(4977941,1505730036,280,'1.2kV'),  
(4977936,1505730036,280,'280VDC'),  
(4977935,1505730036,280,'100V'),  
(4977808,1505730036,280,'N/A'),  
(4979054,1505730036,280,'24VAC/DC')  
                                  
DECLARE @Sql nvarchar(max)  
DECLARE @ConStr nvarchar(max)  
                                                      
SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey ,   
IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' +  case when PATINDEX('%[><=]%', EStrat)>0  then   
case when PATINDEX('%[0-9]%', EStrat)>0  then   
'AcceptedValuesOption_Value '  
else   
'cast(AcceptedValuesOption_Value as varchar(100)) '  end   
when EStrat is null then  
'Name '  
else   
'Name '  
end  ) , case when PATINDEX('%[><=]%', EStrat)>0 then  
case when PATINDEX('%[0-9]%', EStrat)>0  then  
CAST(dbo.udf_RemoveAlphabets(EStrat) AS NVARCHAR(2500))  
else   
CAST( EStrat AS NVARCHAR(2500)) end  
when EStrat is null then  
' is null '  
else  
''+CAST(EStrat AS NVARCHAR(2500)) +''  
end ,')')     
FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue =0  
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')  
                 
----------------                      
SET @Sql= CONCAT(' SELECT PartID, Code, Count(1) as ConCount  
FROM #PartAttributes PM   
INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',  
'Where 1=1 and (', @ConStr, ' ) Group By PartID,Code ' ,  
' Having Count(1)> = ',(SELECT COUNT(1) FROM #Condition))  
                
--print @SQL    
EXEC (@SQL)  

Output:

PartID Code ConCount  
4977808 8535400000 4  
4977935 8535400000 4  
4977936 8535400000 4  
4977941 8535400000 4  
4979054 8535400000 4  

If above is still not working, please provide more sample data of #Condition table and expected output.

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.