error incorrect syntax near , when make dynamic query ?

Question

I work on sql server 2012 i have error syntax near ' but i can't know how to solve error
so please help me

what i need to do
if EStrat is text then use Name
if it EStrat is number use acceptedvaluesoption

dynamic query generate error on end ,
so how to solve issue please

    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
  'cast(AcceptedValuesOption_Value as varchar(100)) '
  else 
  'cast(AcceptedValuesOption_Value as varchar(100))='
  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 #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')

i need final result as below :

 SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
            FROM 
            #PartAttributes PM 
            INNER JOIN  #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey Where (1=1 and   (PM.ZfeatureKey= 1505730001 And Name In('Surge Protector','Surge Protector for Media Lines Only')) Or (PM.ZfeatureKey= 1505730036 And AcceptedValuesOption_Value >1000) ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID  Having Count(1)>= 2

Answer 1

only one thing can you please tell me what incorrect syntax error exist on following statement please
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
'cast(AcceptedValuesOption_Value as varchar(100)) '
else
'cast(AcceptedValuesOption_Value as varchar(100))='
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 #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')

Answer 2

Hi @ahmed salah ,

Syntaxsql of IIF is as below:

IIF( boolean_expression, true_value, false_value )  

Only according to your statement, you could have a try to add on ')' in below red box or other location since the final ')' is missing with IIF function in blue box.

If above is not working, please provide the DDL and sample data so that we could check further.

Thank you for understanding!

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.