error incorrect syntax near '/A' when make dynamic query ?

Question

I work on SQL server 2012 i face issue error incorrect syntax near '/A'
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '/A'.

so How to solve this issue please ?

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(20),
     EEnd NVARCHAR(10)
     )
     insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)
     values
     ('8535400000','1505730036',NULL,'>1000',' '),
     ('8535400000','1505730036',NULL,'>280AV',' '),
     ('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,'280VDC'),
     (4977936,1505730036,280,'280VDC'),
     (4977935,1505730036,280,'280VDC')



             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 Name ' ) , LEFT(EStrat, 1), '''', SUBSTRING(EStrat, 2, LEN(EStrat) -1), ''')')   --ValueName
                      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))
                     EXEC (@SQL)

Answer 1

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(20),
 EEnd NVARCHAR(10)
)
insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)
values
('8535400000','1505730036',NULL,'>1000',' '),
('8535400000','1505730036',NULL,'>280AV',' '),
('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,'280VDC'),
(4977936,1505730036,280,'280VDC'),
(4977935,1505730036,280,'280VDC')

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 Name '), CASE WHEN EStrat = 'N/A' THEN '=''N/A''' WHEN EStrat IS NULL THEN 'IS NULL' ELSE LEFT(EStrat, 1) + '''' + SUBSTRING(EStrat, 2, LEN(EStrat) -1) + '''' END, ')')   --ValueName                          
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))
EXEC (@SQL)

Answer 2

Hi @ahmed salah ，

Please refer below query and check whether it is helpful to you. If not, please provide your desired output.

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 Name ' ) ,   
 CASE WHEN EStrat='N/A' OR ISNULL(EStrat,'')='' THEN '= '''+  ISNULL(EStrat,'') ELSE  LEFT(EStrat, 1)+''''+ SUBSTRING(EStrat, 2, LEN(EStrat) -1) END,  
 ''')')   --ValueName  
 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))  
      
 EXEC (@SQL)  

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.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet