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.
DECLARE @Combined VARCHAR(30) = ''
SELECT @Combined = @Combined + Letter
SELECT 'B' AS Letter, NULL AS Ord
SELECT 'A', 1
SELECT 'C', 3
) AS Data
ORDER BY Ord -- ISNULL(Ord, 1000)
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!