SQL Server의 조건부 WHERE 절

SQL Server의 조건부 WHERE 절

---

where조건절이 필요한 SQL 쿼리를 만들고 있습니다.

다음과 같아야합니다.

SELECT 
    DateAppr,
    TimeAppr,
    TAT,
    LaserLTR,
    Permit,
    LtrPrinter,
    JobName,
    JobNumber,
    JobDesc,
    ActQty,
    (ActQty-LtrPrinted) AS L,
    (ActQty-QtyInserted) AS M,
    ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) AS N
FROM 
    [test].[dbo].[MM]
WHERE 
    DateDropped = 0
            --This is where i need the conditional clause 
    AND CASE
            WHEN @JobsOnHold = 1 THEN DateAppr >=  0
            ELSE  DateAppr != 0
        END

위의 쿼리가 작동하지 않습니다. 이것이 올바른 구문이 아니거나 내가 모르는 다른 방법이 있습니까?

동적 SQL을 사용하고 싶지 않으므로 다른 방법이 있습니까? 아니면 if else다른 where절 에서 동일한 쿼리를 사용 하고 사용하는 것과 같은 해결 방법을 사용해야 합니까?

---

이 시도

SELECT 
    DateAppr,
    TimeAppr,
    TAT,
    LaserLTR,
    Permit,
    LtrPrinter,
    JobName,
    JobNumber,
    JobDesc,
    ActQty,
    (ActQty-LtrPrinted) AS L,
    (ActQty-QtyInserted) AS M,
    ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) AS N
FROM 
    [test].[dbo].[MM]
WHERE 
    DateDropped = 0
    AND (
    (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) 
    OR 
    (ISNULL(@JobsOnHold, 0) != 1 AND DateAppr != 0)
    )

조건부 WHERE에 대한 자세한 내용은 여기에서 확인할 수 있습니다.

---

이걸로 해봐 -

WHERE DateDropped = 0
    AND (
        (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) 
        OR 
        (ISNULL(@JobsOnHold, 0) != 1 AND DateAppr != 0)
    )

---

WHERE 절에서 CASE 표현식을 사용하는 방법에 대한 근본적인 질문에 답하려면 :

First remember that the value of a CASE expression has to have a normal data type value, not a boolean value. It has to be a varchar, or an int, or something. It's the same reason you can't say SELECT Name, 76 = Age FROM [...] and expect to get 'Frank', FALSE in the result set.

Additionally, all expressions in a WHERE clause need to have a boolean value. They can't have a value of a varchar or an int. You can't say WHERE Name; or WHERE 'Frank';. You have to use a comparison operator to make it a boolean expression, so WHERE Name = 'Frank';

That means that the CASE expression must be on one side of a boolean expression. You have to compare the CASE expression to something. It can't stand by itself!

Here:

WHERE 
    DateDropped = 0
    AND CASE
            WHEN @JobsOnHold  = 1 AND DateAppr >= 0 THEN 'True'
            WHEN DateAppr != 0 THEN 'True'
            ELSE 'False'
        END = 'True'

Notice how in the end the CASE expression on the left will turn the boolean expression into either 'True' = 'True' or 'False' = 'True'.

Note that there's nothing special about 'False' and 'True'. You can use 0 and 1 if you'd rather, too.

You can typically rewrite the CASE expression into boolean expressions we're more familiar with, and that's generally better for performance. However, sometimes is easier or more maintainable to use an existing expression than it is to convert the logic.

---

The problem with your query is that in CASE expressions, the THEN and ELSE parts have to have an expression that evaluates to a number or a varchar or any other datatype but not to a boolean value.

You just need to use boolean logic (or rather the ternary logic that SQL uses) and rewrite it:

WHERE 
    DateDropped = 0
AND ( @JobsOnHold = 1 AND DateAppr >= 0 
   OR (@JobsOnHold <> 1 OR @JobsOnHold IS NULL) AND DateAppr <> 0
    )

---

Often when you use conditional WHERE clauses you end upp with a vastly inefficient query, which is noticeable for large datasets where indexes are used. A great way to optimize the query for different values of your parameter is to make a different execution plan for each value of the parameter. You can achieve this using OPTION (RECOMPILE).

In this example it would probably not make much difference, but say the condition should only be used in one of two cases, then you could notice a big impact.

In this example:

WHERE 
    DateDropped = 0
    AND (
    (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) 
    OR 
    (ISNULL(@JobsOnHold, 0) <> 1 AND DateAppr <> 0)
    )
OPTION (RECOMPILE)

Source Parameter Sniffing, Embedding, and the RECOMPILE Options

참고URL : https://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server