T-SQL is returning unexpected results

Question

the following w queries return different results:

 declare @sql nvarchar(max)
 declare @id int = 15
 SET @SQL = '';

							
SELECT @SQL = @SQL + AlterTableSQL  
FROM CustomRegisters_CustomFields
WHERE LogId = @id
        AND ISNULL(AlterTableSQL, '') <> ''
ORDER BY Id;
 
select @sql

and

 declare @sql nvarchar(max)
 declare @id int = 15
 SET @SQL = '';

							
SELECT @SQL = @SQL + AlterTableSQL  
FROM CustomRegisters_CustomFields
WHERE LogId = 15
        AND ISNULL(AlterTableSQL, '') <> ''
ORDER BY Id;
 
select @sql

the only difference is changing a variable to a constant

Answer 1

I see from your comment you're stuck in the pre-SQL 2017 world where STRING_AGG is not an option. In that case, use FOR XML instead for defined behavior:

SELECT @SQL = (SELECT COALESCE(AlterTableSQL,'')  
	FROM CustomRegisters_CustomFields
	WHERE LogId = @id
	ORDER BY id
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)');

SELECT @SQL;
GO

Answer 2

Aggregate string concatenation behavior (e.g. @SQL = @SQL + ...) is undefined. Use STRING_AGG instead, which will work consistently regardless of the execution plan and simplify the query too since you can omit the ISNULL expression.

DECLARE @sql nvarchar(MAX);
DECLARE @id int = 15;
							
SELECT @SQL = STRING_AGG(AlterTableSQL,'') WITHIN GROUP (ORDER BY id)
FROM CustomRegisters_CustomFields
WHERE LogId = @id;
 
SELECT @sql;