為什麼這個游標會以錯誤的順序產生結果？

我正在編寫一些動態 SQL 來辨識，也許如果我覺得夠瘋狂的話，會自動將我的NONCLUSTERED索引轉換為CLUSTERED索引。

下面的 SQL 中的行ORDER BY 1,2,3 DESC;旨在在DROP INDEX...語句之前輸出語句ALTER TABLE...，以便先 DROP NONCLUSTERED 索引，然後添加 CLUSTERED 索引。我必須在第DESC3 列之後添加，以便首先獲得 DROP，然後是 ALTER。這是倒退，除非我失去它！

DECLARE @Server nvarchar(max);
DECLARE @Database nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE @IndexType int;


SET @IndexType = 2; /*  1 is CLUSTERED, 2 is NONCLUSTERED */
SET @Server = 'MyServer';
SET @Database = 'MyDatabase';

SET @cmd = '
   DECLARE @cmd nvarchar(max);
   SET @cmd = ''
   SET NOCOUNT ON;
   DECLARE @IndexInfo TABLE (TableName nvarchar(255), IndexName nvarchar(255), IndexColumnName nvarchar(255));
   INSERT INTO @IndexInfo (TableName, IndexName, IndexColumnName)
   SELECT t.name AS TableName, i.name AS IndexName, c.name AS IndexColumnName /*, t.create_date, ic.*, c.* */
   FROM sys.tables t
       LEFT JOIN sys.indexes i ON t.object_id = i.object_id
       LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
       LEFT JOIN sys.columns c ON i.object_id = c.object_id and ic.column_id = c.column_id
   WHERE i.is_primary_key = 1  
       AND i.type = ' + CAST(@IndexType as nvarchar(max)) + '
   ORDER BY t.create_date desc;
   DECLARE @t1 nvarchar(max);
   DECLARE @t2 nvarchar(max);
   DECLARE @t3 nvarchar(max);
   DECLARE @cmd nvarchar(max);
   DECLARE cur CURSOR FOR
   SELECT TableName, IndexName, 1 AS ExecOrder, ''''DROP INDEX '''' + IndexName + '''' ON '''' + TableName + '''';'''' FROM @IndexInfo I
   UNION ALL 
   SELECT TableName, IndexName, 2 AS ExecOrder, ''''ALTER TABLE '''' + TableName + '''' ADD CONSTRAINT PK_'''' + TableName + ''''_'''' + IndexColumnName + '''' PRIMARY KEY CLUSTERED ('''' + IndexColumnName + '''')'''' + '''';'''' FROM @IndexInfo I
   ORDER BY 1,2,3 DESC;
   OPEN cur;
   FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
   WHILE @@FETCH_STATUS = 0
   BEGIN
       PRINT @cmd;
       FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
   END
   CLOSE cur;
   DEALLOCATE cur;
   '';
   EXEC ' + @Server + '.' + @Database + '.sys.sp_executesql @cmd;
';
PRINT @cmd;

循環內的PRINT語句WHILE確實按照您期望的順序執行，但輸出在sys.sp_executesql返回之前被緩衝。實現細節意味著緩衝的輸出是反向的。

使用RAISERROR (@cmd, 0, 1) WITH NOWAIT;而不是PRINT強制緩衝區在每次呼叫後刷新，以您期望的順序為您提供結果。IIRC 這個NOWAIT技巧只適用於前 500 行的每行。無論如何，所有這些都是無證的東西，隨時可能改變，所以請不要依賴它——我只是為了解釋你所看到的而提到它。

sp_executesql如果您將呼叫替換為，則不會發生逆轉，EXEC (@cmd) AT ' + @Server + '儘管這確實需要USE database前綴為的命令，@cmd並且還需要為 RPC 啟用連結伺服器。這也不是建議，只是顯示輸出反轉是sp_executesql.

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