SQL Server – Stored Procedure Returns Different Results in SSMS vs C#

I have a stored procedure that when executed in SSMS returns different values than when the same SP is executed in code, even a very simple SP call and dump in Linqpad. We believe this started happening after migrating to a new server running SQL Server 2016 Standard.

The stored procedure uses 3 table variables, one of which is updated using a cursor (not best practice).

Debug Steps taken so far:

1. The problem does not occur on dev servers restored from nightly backups

2. I created an identical stored procedure on the same database. The problem did not follow, the new SP returned the same results in SSMS and LINQPAD.

3. I executed sp_recompile on the stored procedure. This seemed to fix the issue, we then saw the same results in SSMS and LINQPAD. However, this was temporary. We recompiled on Friday, the problem returned today (Tuesday).

4. Checking sys.dm_exec_procedure_stats, I do not see that the SP plan changed. I also checked the statement plan, it does not seem to have changed either.

Any ideas what I could check next?

Here is the code. First, I will say that this SP is not up to our standards. This SP can and will be re-written to eliminate the cursor, etc.

However, this is the first time in my career that I have ever seen stored procedure results be different in SSMS vs being called from other code. We upgraded to SQL 2016 about 2 weeks ago. This problem pop up shortly after the upgrade.

The column labeled "BoxX" in the output is where we are seeing the differences. This is the column that is updated in the cursor.

One of the purposes of this SP is to to display Box X of Y. (Box 1 of 2, 2 of 2, etc.)

In SSMS the values in BoxX will be 1,2,1,2,3, etc. In LINQPAD they are 1,1,1,1,1

SSMS Results:

LINQPAD Results:

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 

And here is how we tested in 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(); 

}