SQL Server View produces different result sets
I have a SQL Server View:
SELECT SOPOrderReturn.DocumentNo, StockItem.Code, SLCustomerAccount.CustomerAccountNumber, SOPOrderReturn.CustomerID, SOPDocDelAddress.PostalName, SOPDocDelAddress.AddressLine1, SOPDocDelAddress.AddressLine2, SOPDocDelAddress.AddressLine3, SOPDocDelAddress.AddressLine4, SOPDocDelAddress.PostCode, SOPOrderReturnLine.LineQuantity, SOPOrderReturn.PromisedDeliveryDate, StockItem.SpareNumber2, StockItem.SpareNumber3, StockItem.Name, StockItem.SpareText2, StockItem.SpareText1, StockItem.SpareNumber1 FROM SOPOrderReturn INNER JOIN SOPOrderReturnLine ON SOPOrderReturn.SOPOrderReturnID = SOPOrderReturnLine.SOPOrderReturnID INNER JOIN SOPDocDelAddress ON SOPOrderReturn.SOPOrderReturnID = SOPDocDelAddress.SOPOrderReturnID INNER JOIN SLCustomerAccount ON SOPOrderReturn.CustomerID = SLCustomerAccount.SLCustomerAccountID INNER JOIN StockItem ON SOPOrderReturnLine.ItemCode = StockItem.Code WHERE (NOT (StockItem.Code LIKE '%DELIVERY%')) AND (NOT (StockItem.Name LIKE '%DELIVERY%')) AND (SOPOrderReturn.PromisedDeliveryDate > CURRENT_TIMESTAMP - 3)
When called 'as is' from .net using this query:
SELECT [DocumentNo],[Code], [CustomerAccountNumber], [CustomerID],[PostalName], [AddressLine1],[AddressLine2], [AddressLine3],[AddressLine4], [PostCode],[LineQuantity], [PromisedDeliveryDate], [SpareNumber2], [SpareNumber3], [Name], [SpareText2], [SpareText1], [SpareNumber1] FROM [viwSalesOrdersRecent];
...everything is hunky dory. It returns all the 861 rows I expect to see, which matches what SQL Server says should be in the View.
However, when I call the View (as above) with the following Where clause tacked on it:
WHERE [DocumentNo] = @prmSalesOrderNumber AND [Code] = @prmStockCode;
The query returns the entire dataset, ignoring the existing Where clause inside the View, and I don't understand this behaviour. I'm out of practice with SQL Server, haven't touched it for 3 years, and I'm now back in the thick of it and completely out of my depth.
Any help will be much appreciated.
Ultimately I never solved this, so I ended up chucking it towards Microsoft for them to look at.
They have tentatively confirmed that this is caused by a bug in SQL Server, which they will hopefully remedy with a future update.