While working on some dynamic sql production code we encountered code that’s concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a sql variable to eventually call EXEC(@sql). One thing we noticed was that despite our string concat and our COALESCE checks, only the last record was populating the variable.
The below code is a simplified version to reproduce the results.
IF OBJECT_ID('tempdb..#test') IS NOT NULL BEGIN DROP TABLE #test END CREATE TABLE #test ( tmp_id INT IDENTITY(1,1), error VARCHAR(MAX), error2 VARCHAR(MAX), object_id INT ) DECLARE @sql VARCHAR(MAX) = '' INSERT INTO #test(error, error2, object_id) SELECT TOP 100 CONVERT(VARCHAR(MAX), name), CONVERT(VARCHAR(MAX), name), object_id FROM sys.columns SELECT @sql += ' | '+(SELECT xC.name FROM sys.columns xC WHERE xC.name = T.error AND xC.object_id = T.object_id) + ' | ' + CAST(T.error2 AS VARCHAR(MAX)) FROM #test T ORDER BY T.tmp_id SELECT @sql
The strange thing is there seems to be a combination of factors at play here. If you remove the subquery sys.columns, or the order by, or the error2 column then it works as expected. You can also change the order by to be ordered by error instead of tmp_id and it will also still work.
I’m not looking for a solution as I’ve already rewritten the production code but I’m very interested in the “why” this is happening. I’ve tested on sql server 2008, 2016, 2017, and 2019 server.
Anyone have any ideas as to the cause?
String concatenation in that form combined with
ORDER BY is undefined (and unstable and unreliable). I have seen the same symptom where you only get one result. If you want all the rows, drop the
ORDER BY (ordering won’t be guaranteed, but maybe that’s ok, since your
TOP 100 doesn’t have an
ORDER BY, so that’s not guaranteed to be deterministic either). If you need
ORDER BY, use
XML PATH or
STRING_AGG() instead, depending on version.
SELECT @sql = STUFF(( SELECT ',' + t.error + CONVERT(varchar(12), xC.[object_id]) + ',' + t.error + ',' + t.error + ',' + t.error + ',' + t.error2 + ',' -- carriage returns are good for the soul! + t.error + ',' + t.error + ',' + t.error + ',' FROM #test AS t INNER JOIN sys.columns AS xC ON t.object_id = xC.object_id ORDER BY t.tmp_id -- this is the key to ordering FOR XML PATH, TYPE).value(N'.', N'nvarchar(max)'), 1, 1, N'');
On 2017+ you can use:
SELECT @sql = STRING_AGG(t.error + CONVERT(varchar(11), xC.[object_id]) + ',' + t.error + ',' + t.error + ',' + t.error + ',' + t.error2 + ',' + t.error + ',' + t.error + ',' + t.error, ',') WITHIN GROUP (ORDER BY t.tmp_id) FROM #test AS t INNER JOIN sys.columns AS xC ON t.object_id = xC.object_id;
For more info, see Order by 1 shortens the result set to one row and the links therein.