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

ahmed salah 3,131 Reputation points
2021-11-05T17:43:35.847+00:00

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

146924-image.png

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

I attach ddl scripts
146858-tables-structure.txt

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,560 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Phillips 17,511 Reputation points
    2021-11-05T18:03:20.297+00:00

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

    No comments

  2. Yitzhak Khabinsky 20,016 Reputation points
    2021-11-05T18:15:12.857+00:00

    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 |  
    +------------+----------------------------------------+  
    
    No comments

  3. EchoLiu-MSFT 14,416 Reputation points
    2021-11-08T09:21:30.077+00:00

    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

    No comments