Conditional WHERE clause in SQL Server

问题:

I am creating a SQL query in which I need a conditional where clause.

It should be something like this:

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

The above query is not working. Is this not the correct syntax or is there another way to do this that I don\'t know?

I don\'t want to use dynamic SQL, so is there any other way or do I have to use a workaround like using if else and using the same query with different where clauses?

回答1:

Try this

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)
    )

You can read more about conditional WHERE here.

回答2:

Try this one -

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

回答3:

To answer the underlying question of how to use a CASE expression in the WHERE clause:

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.

回答4:

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
    )