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

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)