Stored Procedure returns different results in SSMS vs C# code

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:

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

-
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.

-
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).

-
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)
dec

It seems that the cursor's intension is to fill MCBoxX with ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY PackageID). But there is no ORDER BY in the cursor so the the rows of @tmp2 may be processed in a way that each row has different OrderNo than its previous, resulting in all row numbers filled with 1, due to the IF (@tmpCompareOrderno <> @tmpMCCustOrderNo) check.

As Scott Hogdin's answer suggests, you can add an explicit ORDER BY in the cursor.

You could also skip the cursor altogether and provide the row numbers during the insert (and have less and cleaner code):

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

StackExchange Database Administrators Q#165758, answer score: 3