Sql Server 字元串 concat 與 order by 和子查詢

在處理一些動態 sql 生產程式碼時，我們遇到了將一堆字元串和一個子查詢連接起來的程式碼，然後是更多的字元串連接。程式碼的目標是填充一個 sql 變數以最終呼叫 EXEC(@sql)。我們注意到的一件事是，儘管我們進行了字元串 concat 和 COALESCE 檢查，但只有最後一條記錄填充了變數。

下面的程式碼是重現結果的簡化版本。

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

奇怪的是，這裡似乎有多種因素在起作用。如果您刪除子查詢 sys.columns、order by 或 error2 列，那麼它會按預期工作。您還可以將 order by 更改為按錯誤排序，而不是 tmp_id，它仍然可以工作。

我不是在尋找解決方案，因為我已經重寫了生產程式碼，但我對發生這種情況的“原因”非常感興趣。我已經在 sql server 2008、2016、2017 和 2019 伺服器上進行了測試。

有人對原因有任何想法嗎？

這種形式的字元串連接ORDER BY是未定義的（並且不穩定且不可靠）。我見過同樣的症狀，你只能得到一個結果。如果您想要所有行，請刪除ORDER BY（無法保證排序，但也許沒關係，因為您TOP 100沒有ORDER BY，因此也不能保證是確定性的）。如果需要ORDER BY，請使用XML PATH或STRING_AGG()代替，具體取決於版本。

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'.[1]', N'nvarchar(max)'), 1, 1, N'');

在 2017+ 上，您可以使用：

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;

有關詳細資訊，請參閱Order by 1 將結果集縮短為一行以及其中的連結。

引用自：https://dba.stackexchange.com/questions/260573