Problem with string concatenation query

Transact-SQL
1

Hi,

I've used the SELECT @VarCharValue = @VarCharValue +Table.ColumnValue + ';' FROM... many times to build a long string from a bunch of rows. In a new project, I have an order by column that may be null (because of a left join) so I tried to use a ISNULL in the order by. It completely breaks the code.

Any ideas?

DECLARE @Combined VARCHAR(30) = ''
SELECT @Combined = @Combined + Letter
FROM (
  SELECT 'B' AS Letter, NULL AS Ord
  UNION
  SELECT 'A', 1
  UNION
  SELECT 'C', 3
) AS Data
ORDER BY Ord -- ISNULL(Ord, 1000)

SELECT @Combined

This query returns 'BAC' because the NULL becomes the lowest value. This is working as it is supposed to.

If I replace the ORDER BY Ord with ORDER BY ISNULL(Ord, 1000) so my null data appears at the end of the string, it doesn't work as expected. The query now just returns 'B'. Very weird!!! I've tried nesting the query into another level and doing the ISNULL, but the query still only returns 'B'. I could insert the inner query into a temp table and do the ISNULL during the insert, then concatenate from the temp table, but I was hoping to not have to use a temp table.

Any thoughts, help, ideas would be appreciated!

Thanks!

2

I came to the same conclusion.

Started off thinking "Yeah Yeah this will be easy", Cut and Pasted your code and ran it and ... Bingo! you are quite right.

My guess is that SQL is short-circuiting something, or the concatenation isn't happening for each row for some reason.

If I change the

SELECT @Combined = @Combined + Letter

to

SELECT *

the three rows are output as expected.

FWIW I use a different approach for forcing the sorting order of NULL values ("horses for courses" though), but I tried it and although that works it doesn't solve this problem either

ORDER BY CASE WHEN Ord IS NOT NULL THEN 1 ELSE 2 END -- Sort NULL values last
	, Ord
3

Interesting.

If you do not want to use a temp table, FOR XML seems to work:

DECLARE @Combined varchar(30) = '';
WITH Data(Letter, Ord)
AS
(
    SELECT 'B', NULL
    UNION ALL
    SELECT 'A', 1
    UNION ALL
    SELECT 'C', 3
)
SELECT DISTINCT @Combined = X.Combined
FROM Data D1
    CROSS APPLY
    (
        SELECT 
        (
            SELECT Letter
            FROM Data D2
            ORDER BY ISNULL(D2.Ord, 1000)
            FOR XML PATH(''), TYPE).value('.', 'varchar(30)'
        )
    ) X (Combined);
SELECT @Combined;
4

Note that queries of the form:

SELECT @var = @var + colname

are not officially supported (though they often work). Use FOR XML instead.

5

If you can move the ISNULL inside the derived table query, you may be OK:

DECLARE @Combined VARCHAR(30) = ''
SELECT @Combined = @Combined + Letter
FROM	(
  SELECT 'B' AS Letter, ISNULL(NULL, 1000) AS Ord
  UNION
  SELECT 'A', 1
  UNION
  SELECT 'C',	3
) AS Data
ORDER BY Ord
6

Found the reference:
Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location