SQL – concatenate strings in variable while loop with +=

I can’t concatenate in this example bellow! When I loop I get my 2 correct results. When I concatenate @MaterialCompositionEn += ‘, ‘ it works fine When I try to concatenate @MaterialCompositionEn += same query to get the 2nd row, I have a null!

    DECLARE @MaterialCompositionId int = 475;
    DECLARE @MaterialCompositionKey nvarchar(50) = '202071512324138';
    DECLARE @Records nvarchar(250);
    DECLARE @RecordProceed int;
    DECLARE @MaterialCompositionEn nvarchar(500);

    SET @Records =  (SELECT STRING_AGG(Id, ',') FROM MaterialCompositions mc WHERE mc.MaterialCompositionId = @MaterialCompositionId)

    WHILE len(@Records) > 0
    BEGIN

        SET @RecordProceed = CAST(LEFT(@Records,4) AS int)
        if @RecordProceed > 0
            BEGIN
                SET @Records = REPLACE(@Records,substring(@Records, 1, 4),'')
            END
        if len(@Records) > 4
            BEGIN
                SET @Records = REPLACE(@Records,substring(@Records, 1, 1),'')
            END

        if len(@MaterialCompositionEn) > 0
            BEGIN
                SET @MaterialCompositionEn += ', '
            END
PRINT 'MaterialCompositionEn1: ' +  @MaterialCompositionEn

        SET @MaterialCompositionEn =
            (SELECT COALESCE (CAST(MaterialProportion AS nvarchar) + '% ', '') +
                (SELECT mp.MaterialPrimaryEn + 
                    COALESCE(
                        (SELECT ' (' + ms.MaterialSecondaryEn + ')' AS MS1 FROM dbo.MaterialSecondaries AS ms WHERE ms.Id = mc.MaterialSecondaryId)
                    , '')
                FROM dbo.MaterialPrimaries AS mp WHERE mp.Id = mc.MaterialPrimaryId)
            FROM MaterialCompositions mc WHERE mc.Id = @RecordProceed
            )

PRINT 'MaterialCompositionEn2: ' +  @MaterialCompositionEn
    END

Result:

MaterialCompositionEn2: 20% Cashmere
MaterialCompositionEn1: 20% Cashmere, 
MaterialCompositionEn2: 80% Wool

Now when I change to:

SET @MaterialCompositionEn +=
            (SELECT COALESCE......

I am expecting 20% Cashmere, 80% Wool instead my 3 prints are NULL I tried to CAST but won’t help.

Any idea? Thanks in advance

Answer

I’m guessing there is a much simpler way to do what you want. However, I think the problem is that you need to initialize the string. So at the top of the code block put:

SET @MaterialCompositionEn = '';