参数的sql case开关

ftf50wuq  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(256)

我有一个参数,可以是3个可能值0、2或null中的1个(这些是枚举的值0处于挂起状态,1处于审核中,2处于关闭状态)。如果为空,则应检索所有状态,但如果为2,则应检索所有已关闭的状态;如果为0,则应检索挂起或正在审阅的状态。最后一个意味着我要检索状态为0或1的记录。我有以下代码到目前为止,并尝试与案件开关,但它不工作。

SELECT * FROM PurchaseOrder
    WHERE (CreationDate >= @StartDate AND CreationDate <= @EndDate)
    AND (@POStatus IS NULL OR [Status] = @POStatus)
    AND (@POStatus IS NULL OR [Status] = CASE @POStatus WHEN 0 THEN 1 END)
    AND (@PurchaseOrderIdSearch IS NULL OR PurchaseOrderId LIKE @PurchaseOrderIdSearch)
    AND EmployeeId = @EmployeeId
    ORDER BY CreationDate DESC, PurchaseOrderId DESC

我通过添加if语句解决了这个问题,我在寻找一种更简单的方法来避免重复,现在是这样的:

IF @POStatus = 0
    BEGIN
        SELECT * FROM PurchaseOrder
        WHERE (CreationDate >= @StartDate AND CreationDate <= @EndDate)
        AND (([Status] = @POStatus)
        OR ([Status] = 1))
        AND (@PurchaseOrderIdSearch IS NULL OR PurchaseOrderId LIKE @PurchaseOrderIdSearch)
        AND EmployeeId = @EmployeeId
        ORDER BY CreationDate DESC, PurchaseOrderId DESC
    END
    ELSE
    BEGIN
        SELECT * FROM PurchaseOrder
        WHERE (CreationDate >= @StartDate AND CreationDate <= @EndDate)
        AND (@POStatus IS NULL OR [Status] = @POStatus)
        AND (@PurchaseOrderIdSearch IS NULL OR PurchaseOrderId LIKE @PurchaseOrderIdSearch)
        AND EmployeeId = @EmployeeId
        ORDER BY CreationDate DESC, PurchaseOrderId DESC
    END
ocebsuys

ocebsuys1#

我想你是在找

DECLARE @MyParam INT; --Try to set 0, 1 or 2 

SELECT *
FROM
(
  VALUES
  (0),
  (1),
  (2)
) T(Value)
WHERE (Value = @MyParam) OR (@MyParam IS NULL);

这里有一个db<>小提琴来看看它是如何工作的。
更新:
好像你在找

DECLARE @MyParam INT; --Try to set 0, 1 or 2 

SELECT *
FROM
(
  VALUES
  (0),
  (1),
  (2)
) T(Value)
WHERE 
(
  CASE WHEN @MyParam = 0 OR @MyParam = 1 THEN 1 ELSE 0 END = 1
  AND Value IN(0, 1)
)
OR
(Value = @MyParam AND @MyParam = 2)
OR 
@MyParam IS NULL;

这是一把小提琴

相关问题