儲存過程在 SSMS 與 C# 程式碼中返回不同的結果

我有一個儲存過程，在 SSMS 中執行時返回的值與在程式碼中執行相同 SP 時返回的值不同，即使是在 Linqpad 中非常簡單的 SP 呼叫和轉儲。我們相信這在遷移到執行 SQL Server 2016 Standard 的新伺服器後開始發生。

儲存過程使用 3 個表變數，其中一個使用游標更新（不是最佳實踐）。

到目前為止採取的調試步驟：

1. 從夜間備份恢復的開發伺服器上不會出現此問題
2. 我在同一個數據庫上創建了一個相同的儲存過程。問題沒有出現，新的 SP 在 SSMS 和 LINQPAD 中返回了相同的結果。
3. 我在儲存過程上執行了 sp_recompile。這似乎解決了問題，然後我們在 SSMS 和 LINQPAD 中看到了相同的結果。然而，這是暫時的。我們週五重新編譯，今天（週二）問題又回來了。
4. 檢查 sys.dm_exec_procedure_stats，我沒有看到 SP 計劃發生了變化。我還檢查了聲明計劃，它似乎也沒有改變。

有什麼想法我可以檢查一下嗎？

這是程式碼。首先，我會說這個SP不符合我們的標準。該 SP 可以並且將被重寫以消除游標等。

然而，這是我職業生涯中第一次看到儲存過程結果在 SSMS 中與從其他程式碼呼叫時不同。我們大約在 2 週前升級到 SQL 2016。升級後不久就會出現此問題。

輸出中標記為“BoxX”的列是我們看到差異的地方。這是在游標中更新的列。

此 SP 的目的之一是顯示 Y 的 Box X。（Box 1 of 2、2 of 2 等）

在 SSMS 中，BoxX 中的值將為 1、2、1、2、3 等。在 LINQPAD 中，它們為 1、1、1、1、1

SSMS 結果：

LINQPAD 結果：

CREATE Procedure [dbo].[SP1] as

Begin
 declare @tmpMCCustOrderNo varchar(10), @tmpCompareOrderno varchar(10), @tmpMCPackageID varchar(21), @tmpCurrentMC int
 declare @tmp1 TABlE(OrderNo varchar(10), MCTotCnt int)
 declare @tmp2 TABLE(Orderno varchar(10),PackageID varchar(21), MCBoxX int)

 --Build list of orders with packages in TABLE2
 Insert into @tmp1(OrderNo, MCTotCnt)
 Select    s.CustOrderNo, count(Distinct s.PackageID)
 From  DATABASE1..TABLE1 s
 Where s.CustOrderNo in (Select Distinct CustOrderNo from DATABASE1..TABLE1 where PackageID in (select PackageID from TABLE2))
     and IsNull(s.BoxType, '') <> ''
 Group     By s.CustOrderNo

 Insert into @tmp2(OrderNo,PackageID,MCBoxX)
     Select distinct s.CustOrderNo, s.PackageID, 0
 From DATABASE1..TABLE1 s, @tmp1 l
     Where s.CustOrderNo = l.OrderNo and IsNull(s.BoxType, '') <> ''
 Order By s.CustOrderNo,s.PackageID

 --Cursor to increment counter (@tempCurrentMC)
 DECLARE c_EX CURSOR FOR
 SELECT Orderno,PackageID FROM @tmp2
 OPEN c_EX
 FETCH NEXT FROM c_EX INTO @tmpMCCustOrderNo,@tmpMCPackageID
 SELECT @tmpCompareOrderNo = @tmpMCCustOrderNo
     SELECT @tmpCurrentMC = 0
     WHILE @@FETCH_STATUS = 0
     BEGIN
         IF (@tmpCompareOrderno <> @tmpMCCustOrderNo)
             BEGIN
                 SELECT @tmpCurrentMC = 1
                     SELECT @tmpCompareOrderno = @tmpMCCustOrderNo
             END
             ELSE
             BEGIN
                 SELECT @tmpCurrentMC = @tmpCurrentMC + 1
             END
         UPDATE @tmp2 SET MCBoxX = @tmpCurrentMC WHERE OrderNo = @tmpMCCustOrderNo and PackageID = @tmpMCPackageID
         FETCH NEXT FROM c_EX INTO @tmpMCCustOrderNO, @tmpMCPackageID
     END
     CLOSE c_EX
     DEALLOCATE c_EX

 declare  @tmp3 table(SalesID varchar(20), Shipper Varchar(50), PackageID Varchar(21), ShipToName Varchar(100), UCC128 Varchar(50), Priority int, status varchar(20), 
   LastUpdate datetime, shipdate datetime, pallets int, packages int, mcboxY int)

 --Gather other order information into another table variable
 Insert into @tmp3 (SalesID, Shipper, PackageID, ShipToName, UCC128, Priority, status, LastUpdate, shipdate, pallets, packages, mcboxY)
 Select Distinct i.ax_salesid as SalesID, i.ShipMethod,  m.PackageID, i.ShipToName, m.UCC128, m.Priority Priority, m.MCHQStatus Status, m.LastUpdate, i.ShipDate ShipDate,  
   COUNT(Distinct s.ucc128) Pallets, COUNT(Distinct S.packageid) Packages, T2.MCTotCnt MCBoxY  
 From DATABASE2..TABLE2 m  
   Inner Join DATABASE1..TABLE3 i  on LEFT (m.packageid, 6) = i.CustOrderNo  
   Inner Join DATABASE1..TABLE1 s on S.CustOrderNo = I.CustOrderNo  
   Left Join @tmp1 T2 on T2.OrderNo = S.CustOrderNo    
 Group by i.ax_salesid , i.ShipMethod, m.PackageID, i.ShipToName, m.UCC128, m.Priority, m.MCHQStatus, m.LastUpdate, i.ShipDate , T2.MCTotCnt 
 Order by m.Priority, i.AX_SalesID, m.PackageID 

 --Return Data
 Select MCR.SalesID, MCR.Shipper, MCR.PackageID, MCR.ShipToName, 
   MCR.UCC128, MCR.Priority, MCR.Status, MCR.LastUpdate, MCR.ShipDate, MCR.Pallets, MCR.Packages, MCM.MCBoxX as BoxX, MCR.mcboxY as OfY 
 From @tmp3 MCR
 Inner Join @tmp2 MCM on 
   MCM.PackageID = MCR.PackageID
 Order by MCR.Priority, MCR.SalesID, MCM.MCBoxX, MCR.mcboxY  


END 

以下是我們在 LINQPAD 中的測試方式

void Main()
{


   var testTable = new DataTable();
   SqlConnection con = new SqlConnection("Data Source=server1;Initial Catalog=Database1;Integrated Security=True");
   SqlCommand cmd = new SqlCommand("exec Database1..SP1", con);
   SqlDataAdapter sda = new SqlDataAdapter(cmd);
   sda.Fill(testTable);
   testTable.Dump(); 

}

我同意@ypercube 關於ORDER BY游標上 no 的說法。

您的項目符號 1、2、3 都指向可能的新執行計劃。

這段程式碼是可疑的部分：

Insert into @tmp2(OrderNo,PackageID,MCBoxX)
     Select distinct s.CustOrderNo, s.PackageID, 0
 From DATABASE1..TABLE1 s, @tmp1 l
     Where s.CustOrderNo = l.OrderNo and IsNull(s.BoxType, '') <> ''
 Order By s.CustOrderNo,s.PackageID

顯然，此過程的作者假設可以使用 an 載入表變數，Order By並且游標會神奇地按該順序從該表變數中提取數據。退房沒有安全帶 - 期待沒有 ORDER BY的訂單。

修改游標以包含 Orderno、PackageID 的 ORDER BY 並讓我們知道這是否能解決您的問題。

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