How to add Feature Name and Feature Value stuff on dynamic query separated by $ for table All Data ?

Question

I work on SQL server 2012 I face issue when add feature KEY on stuff on dynamic query

SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,  
 STUFF(    
 (SELECT DISTINCT ''$'' + QUOTENAME(PM.ZfeatureKey) + ''    
  FROM Condition C       
  WHERE C.[CodeTypeID] = Co.[CodeTypeID] AND C.Code=Co.Code       
  FOR XML PATH (''))      
  , 1, 1, '')  AS FeatureName,  
FROM   
PartAttributes PM   
INNER JOIN 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 Condition))  
  
EXEC (@SQL)   

I get error

Msg 174, Level 15, State 1, Line 2
The stuff function requires 4 argument(s).
Msg 105, Level 15, State 1, Line 10
Unclosed quotation mark after the character string ') ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID Having Count(1)>= 1'.

desired result as below

script ddl found on below link
https://www.mycompiler.io/view/3LncvaR

I attach ddl scripts
146858-tables-structure.txt

Answer 1

Is the Condition table a temporary table? The temporary table in the loop should be created in advance.

Then output variables to troubleshoot errors.Like this:

    DECLARE @Code VARCHAR(20)
    DECLARE @ZPID INT
    DECLARE @Sql nvarchar(max)
    DECLARE @Con nvarchar(max)
    DECLARE @ConStr nvarchar(max)

    WHILE (Select Count(*) From Codes with(nolock) where Proceed =0 ) > 0
    BEGIN

    SELECT Top 1 @ZPID=ZPLID, @Code=Code  From Codes with(nolock) where Proceed=0 

    INSERT INTO #Condition 
    SELECT * 
    FROM Gen G with(nolock) 
    WHERE g.Code=@Code AND G.ZPLID=@ZPID AND G.ZfeatureKey IS NOT NULL

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

    PRINT @Con
    END

Regards
Echo

Answer 2

Hi @ahmed salah ,

Please try the following conceptual example.
If it working for you, you can incorporate it into your real life code.

The answer is following a minimal reproducible example pattern.
You copy it as-is to SSMS, and it is working there.

SQL

-- DDL and sample data population, start  
DECLARE @mockTbl TABLE (  
   ID INT IDENTITY(1,1) PRIMARY KEY  
   , [Code] VARCHAR(30)  
   , [Feature] VARCHAR(30)  
);  
INSERT INTO @mockTbl ([Code], [Feature])  
VALUES ('8541100000', 'Product Shape Type')  
   , ('8541100000', 'White color')  
   , ('8541100050', 'Product Shape Type')  
   , ('8541100050', 'Family Package')  
   , ('8541100050', 'Type');  
-- DDL and sample data population, end  
  
-- before  
SELECT * FROM @mockTbl;  
  
DECLARE @separator CHAR(1) = '$';  
  
-- after  
SELECT DISTINCT c.Code,   
    STUFF(  
    (SELECT @separator + CAST(Feature AS VARCHAR(30)) AS [text()]  
    FROM @mockTbl AS O  
    WHERE O.Code = C.Code  
    ORDER BY Code DESC  
    FOR XML PATH('')), 1, 1, NULL) AS FeaturesList  
FROM @mockTbl AS C;  

Output

+------------+----------------------------------------+  
|    Code    |              FeaturesList              |  
+------------+----------------------------------------+  
| 8541100000 | Product Shape Type$White color         |  
| 8541100050 | Product Shape Type$Family Package$Type |  
+------------+----------------------------------------+  

Answer 3

If you Print @alenzi and see what is generated. Then you can fix it.