SQL Server View produces different result sets

Question

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.

Answer 1

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.

Answer 2

I would suggest you to add isnull in view as well as in query on view. Null values affects result set at very much extent . Check code below

        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 (isnull(StockItem.Code,'') LIKE '%DELIVERY%')) AND
               (NOT (isnull(StockItem.Name,'') LIKE '%DELIVERY%')) AND 
               (SOPOrderReturn.PromisedDeliveryDate > CURRENT_TIMESTAMP - 3)



        SELECT [DocumentNo],[Code],
               [CustomerAccountNumber],
               [CustomerID],[PostalName],
               [AddressLine1],[AddressLine2],
               [AddressLine3],[AddressLine4],
               [PostCode],[LineQuantity],
               [PromisedDeliveryDate],
               [SpareNumber2],
               [SpareNumber3],
               [Name],
               [SpareText2],
               [SpareText1],
               [SpareNumber1]
        FROM [viwSalesOrdersRecent]
        WHERE isnull([DocumentNo],'') = isnull(@prmSalesOrderNumber,'')
         AND isnull([Code],'') = isnull(@prmStockCode,'')