Stored Procedure returns different results in SSMS vs C# code

Question

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: SSMS RESULTS

LINQPAD Results: enter image description here

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(); 
}

• Steve: Different Values. All data is identical except for one column. That one column contains different data (box count per order).
• Scott Hodgin - Retired: Could it be that SET OPTIONS are different between LINQPAD and SSMS? - technet.microsoft.com/en-us/library/ms175088(v=sql.105).aspx
• Steve: Thanks for the suggestion, but ANSI_NULLS is set to ON for both connections.
• Greg: Probably not the issue, but are the 2 connections running in the same schema? In the linqpad code you're calling database1..sp1 without specifying the schema
• ypercubeᵀᴹ: The cursor has no ORDER BY. That might be a reason for different results across executions (the cursor updates this column that shows differences).

Answer 1

I agree with what @ypercube is saying about no ORDER BY on the cursor.

Your bullet items 1, 2, 3 all point to possible new execution plans.

This code is the suspicious part:

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

Apparently, the author of this procedure assumed that a table variable could be loaded using an Order By and the cursor would magically pull the data from that table variable in that order. Check out No Seatbelt – Expecting Order without ORDER BY.

Modify the cursor to include an ORDER BY for Orderno,PackageID and let us know if that solves your problem.

• Steve: Thank You, I skipped right over that and didn't notice the missing order by. I added it to my SP and it is now showing the expected results. I believe this is the answer, but I am still confused as to how I would see different results in SSMS vs C#. What could cause the results to be different in the different methods of calling the SP?
• Scott Hodgin - Retired: I really think that two different execution plans were generated, possibly due to different SET OPTIONS by the C# and SSMS. I don't really have a way to prove that since I don't have access to your system. I'd be curious as the to execution_count from sys.dm_exec_query_stats when running both the C# and the SSMS version.
• ypercubeᵀᴹ: You may get different execution plans for various reasons. You may get results in different order, even from the same connection and the same plan, when you aren't using ORDER BY. It didn't happen only by accident. In a way, you are lucky that this happened and you noticed!
• Steve: Update: to close the loop on this, we added the order by and left the SP in production for a little over 24 hours. The issue never reappeared so I am confident this was the problem. Thank you all for your help. Today we also have rewritten the SP using CTEs instead of table variables and ROWNumber-Over instead of a cursor. Much cleaner.

Answer 2

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 ;

• ypercubeᵀᴹ: I can't be sure but the @tmp1 table seems unnecessary, too.