时间戳

首页 > 解决方案 > SQL Server - 存储过程意外结果

sql - SQL Server - 存储过程意外结果

问题描述

CREATE PROC [dbo].[SP_GetListOfGoodsReceiptNote] (@MinDate AS DATETIME, @MaxDate AS DATETIME, @CompanyID AS INT, @ProductID AS INT = NULL, @TradePartnerID AS INT = NULL, @PurchaseOrderID AS INT = NULL)
AS
BEGIN
    SELECT 
        CVH.ID, CVH.VoucherTypeID, CVH.VoucherDate, CVH.VoucherNumber, CVH.CompanyID, CVH.ReferenceVoucherID, CVH.VoucherAmount, CVH.VoucherStatus, CCP.TradePartnerID, CPMR.PartnerName
    FROM
        CruxVoucherHeader CVH
        LEFT OUTER JOIN CruxCorePurchase CCP ON CCP.VoucherHeaderID = CVH.ID
        LEFT OUTER JOIN CruxPartnerMaster CPMR ON CCP.TradePartnerID = CPMR.ID
        LEFT OUTER JOIN CruxVoucherBody CVB ON CVB.VoucherHeaderID = CVH.ID
    WHERE 
        CVH.VoucherDate >= @MinDate 
        AND CVH.VoucherDate <= @MaxDate
        AND CVH.CompanyID = @CompanyID
        AND CVH.VoucherTypeID = 3
        AND @ProductID IS NULL OR CVB.ProductID = @ProductID        
        AND @TradePartnerID IS NULL OR CCP.TradePartnerID = @TradePartnerID
        AND @PurchaseOrderID IS NULL OR CVH.ReferenceVoucherID = @PurchaseOrderID
END

我在从上述存储过程中获得预期结果时遇到了一些问题。如果您看到，我已经使用 CVH.VoucherTypeID = 3 过滤了数据，但仍然由于某些未知原因，我得到了附加结果。我在编写存储过程时是否犯了任何概念上的错误。?

这就是我执行存储过程的方式。

EXEC SP_GetListOfGoodsReceiptNote '2020/01/01','2021/02/02', 2, NULL, 74, NULL

执行存储过程的结果

标签： sqlsql-serverstored-procedures

解决方案

由于And运算符优先于Or运算符，因此，如果存在表达式 A = B and C is null or C = D ，则and运算符将在 or运算符部分之前进行评估

因此，如果您需要先or评估零件，则需要放在括号中A = B and (C is null or C = D)

所以该WHERE子句看起来需要括号，如下所示：

WHERE CVH.VoucherDate >= @MinDate AND
      CVH.VoucherDate <= @MaxDate AND
      CVH.CompanyID = @CompanyID AND
      CVH.VoucherTypeID = 3 AND
      (@ProductID IS NULL OR CVB.ProductID = @ProductID) AND        
      (@TradePartnerID IS NULL OR CCP.TradePartnerID = @TradePartnerID) AND
      (@PurchaseOrderID IS NULL OR CVH.ReferenceVoucherID = @PurchaseOrderID)

有关运算符优先级的更多信息，请点击此链接

推荐阅读