Concatenating strings in iterating loop: += does not work as expected

Question

I have local table and string that must be populated from its values:

    DECLARE @#SomeTable  TABLE ( some columns ..)
    DECLARE @SomeString  varchar(8000) = 'init string'

While iterating it

    WHILE EXISTS(SELECT * FROM  @#SomeTable)
     BEGIN
        // [somecolumn] is declared temp variable
        SELECT TOP 1 @somecolumn = somecolumn FROM  @#SomeTable

        PRINT 'before ' + @SomeString // 'init string'
        PRINT [some values from SomeTable]  // this OK
        SET @SomeString += [some values from SomeTable] 
        PRINT 'after ' +  @SomeString //'init string' UPDATE NOT TAKE PLACE!!!!

        DELETE  @#SomeTable Where somecolumn = @somecolumn
     END

I find that concatenation fails. Why?

EDIT:

Here is piece of original code:

    /*Represents [WHERE] clause for retrieving values from specifyed range*/
DECLARE @WHEREclause nchar(1000) = 'WHERE '
/*Represents [ORDER BY] clause for sorting in right order and direction {ASC|DESC}*/
DECLARE @ORDERBYclause nchar(1000) = 'ORDER BY '
/*Dynamic query that returns end result*/
DECLARE @sqlCmd varchar(8000) = 
'SELECT 
        img,
        capacity,
        price,
        Id
 FROM HDD  '
/* -a- filling table for input values*/
INSERT INTO @#SequenceTable(columnName,columnValue,comparator,isASC,columnOrder)
SELECT 
    columnName,
    columnValue,
    comparator,
    isASC,
    ROW_NUMBER() OVER (ORDER BY  outOrder) AS columnOrder
FROM
(
        SELECT 'buffer' as columnName, CAST(@buffer AS nchar(20)) as columnValue, @bufferCmp as comparator, @bufferASC as isASC, @bufferOrder as outOrder
    UNION  
        SELECT 'capacity', CAST(@capacity AS nchar(20)), @capacityCmp, @capacityASC, @capacityOrder  
    UNION  
        SELECT 'price', STR(@price,20,2),  @priceCmp, @priceASC, @priceOrder
    UNION  
        SELECT 'angle_speed', CAST(@angleSpeed AS nchar(20)), @angleSpeedCmp ,@angleSpeedASC,@angleSpeedOrder
) AS AnyName
ORDER BY columnOrder 

/*---/a-----------------------------------------------------------------------------------*/

/*variables for above fields*/
DECLARE @columnName nchar(20)
DECLARE @comparator char
DECLARE @columnValue nchar(20)
DECLARE @isASC char

WHILE EXISTS(SELECT * FROM  @#SequenceTable)
BEGIN

    SELECT TOP 1 @columnName = columnName FROM  @#SequenceTable
    SELECT TOP 1 @comparator = comparator FROM  @#SequenceTable 
    SELECT TOP 1 @columnValue = columnValue FROM  @#SequenceTable 
    SELECT TOP 1 @isASC = isASC FROM  @#SequenceTable 

    IF @WHEREclause != 'WHERE '
        BEGIN
            SET @WHEREclause += ' AND '
        END
    PRINT 'before ' + @WHEREclause
    PRINT CONCAT(RTRIM(@columnName), @comparator, @columnValue)
    SET @WHEREclause += CONCAT(RTRIM(@columnName), @comparator, @columnValue)
    PRINT 'after ' +  @WHEREclause

    IF @ORDERBYclause != 'ORDER BY '
        BEGIN
            SET @ORDERBYclause += ','
        END
    IF @isASC = '1'
       SET @ORDERBYclause += CONCAT(RTRIM(@columnName),' ASC ')
    ELSE
       SET @ORDERBYclause += CONCAT(RTRIM(@columnName),' DESC ')

    Delete  @#SequenceTable Where columnName = @columnName

END

And here is piece of print result:

        before WHERE 
        angle_speed=7400 
        after WHERE 

+= does not work as expected. If I use

@SomeString = 'some value' 

then the update is visible, but if I instead use

@SomeString += 'some value' 

update is not visible

Solution 1

I can assure you that there is no bug in SQL Server and that += works exactly as expected. I tried the following code:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000);

WHILE EXISTS (SELECT * FROM  @#SomeTable)
BEGIN
    SELECT TOP 1 @somecolumn = somecolumn FROM @#SomeTable;

    SET @SomeString += @somecolumn;

    PRINT @SomeString; -- Works fine!!!

    DELETE  @#SomeTable Where somecolumn = @somecolumn;
END

And here are my results:

init stringa
init stringabbb
init stringabbbccccc

Since it's impossible to tell exactly what you're doing in your code (you've obfuscated the most important parts), maybe you could start from there? Surely either you have a NULL value in the table, or you're assigning incorrectly, or you're assigning to the wrong variable. Again, impossible to tell, because you've hidden the key parts of your code!

Also, since you don't seem to care about order, you can also do this without looping:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000);

SELECT @SomeString += somecolumn FROM @#SomeTable;

PRINT @SomeString;

Result:

init stringabbbccccc

If you care about order, you can still do this without looping - use an XML trick to concatenate in that order, and then append it to the init string afterward:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000) = '';

SELECT @somecolumn = (SELECT '' + somecolumn FROM @#SomeTable
ORDER BY somecolumn DESC
FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)');

PRINT @SomeString + @somecolumn;

Result:

init stringcccccbbba

On more modern versions (SQL Server 2017+), you can do this:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000);

SELECT @somecolumn = STRING_AGG(somecolumn, '')
  WITHIN GROUP (ORDER BY somecolumn DESC)
  FROM @#SomeTable;

PRINT @SomeString + @somecolumn;

Solution 2

Any calculation in SQL Server between two values results in NULL if one of the values involved is NULL. In your case, @SomeString is not initialized. That means its value is NULL. Hence SET @SomeString =+ 'somevalue' results in NULL.

To solve this, initialize your variables to '' in the beginning.

Also, you have this line of code:

SET @somecolumn += [some values from SomeTable]

But you probably meant to have this:

SET @SomeString += [some values from SomeTable]

Solution 3

I have the same issue and this is certainly a known issue with T-SQL. It is explained in detail here: https://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/

To summarise:

The variable assignment with SELECT statement is a proprietary syntax (T-SQL only) where the behavior is undefined or plan dependent if multiple rows are produced.

Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if there are workarounds.

The ONLY guaranteed mechanism are the following: 1. Use cursor to loop through the rows in specific order and concatenate the values 2. Use for xml query with ORDER BY to generate the concatenated values 3. Use CLR aggregate (this will not work with ORDER BY clause)