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.

Answers


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.


Need Your Help

Windows Live Id Authentication for Windows Phone 7

windows-phone-7 windows-live-id

I'm developing a Windows Phone 7 app but don't want to re-implement my own identity / authentication service - espcially when we know the user will be signed in with a Windows Live account. But, I

Excel VBA to move data from table to table based on column name

excel vba excel-vba

I created a procedure that loops in all header columns of table 1 and search for the same header column in table 2 to update relveant cells based on matched column name.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.