SQL - concatenate strings in variable while loop with +=

Question

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 1

   SET @MaterialCompositionEn =
            SELECT 
                CONCAT(
                  mp.MaterialPrimaryEn, ' ', MaterialProportion, '% ', --always show primary
                  ', ' + ms.MaterialSecondaryEn + CONCAT(100 - MaterialProportion, '%')  --sometimes show secondary
                ) 
            FROM 
                MaterialCompositions mc 
                INNER JOIN dbo.MaterialPrimaries mp ON mp.Id = mc.MaterialPrimaryId
                LEFT JOIN dbo.MaterialSecondaries ms ON ms.Id = mc.MaterialSecondaryId
                WHERE mc.Id = @RecordProceed
            )

Something like this might be neater.. Note that I'm not clear where MaterialProportion comes from (I suspect MaterialCompositions), so this perhaps isn't a solution, just a note as to how you might use CONCAT/avoid having boatloads of nested selects. The use of INNER/OUTER join links compositions and definitely a primary, possibly a secondary material. If the secondary material is null then the aim is to hide it with a mix and match of CONCAT and +

CONCAT treats nulls as empty strings, where as + causes the whole expression to become null. This can be useful to mix and match e.g. in something like ', ' + ms.MaterialSecondaryEn + CONCAT(100 - MaterialProportion, '%'):

• the CONCAT(100 - MaterialProportion, '%') would be 20% (if the primary material was 80%) but
• the ', ' + ms.MaterialSecondaryEn + CONCAT(...) as a whole is NULL if MaterialSecondaryEn IS NULL from a left join fail to match, so where there is only a primary material, a the string describing the secondary should disappear entirely as NULL, which the outer CONCAT handles as an empty string

Answer 2

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 = '';