How to Implement a Conditional WHERE Clause in SQL Server Without Dynamic SQL?

Conditional WHERE clause in SQL Server

Question:

How to implement a conditional WHERE clause in SQL Server without using dynamic SQL or creating multiple queries with different clauses?

Answer:

You can use the following methods:

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

In this query:

The
• ISNULL() function checks whether the @JobsOnHold parameter is empty and returns 0 if it is empty.
• The conditional expression in parentheses checks the value of the @JobsOnHold parameter. If the value is 1, the DateAppr column must be greater than or equal to 0. If the value is not 1, the DateAppr column must not be equal to 0.
The
• AND operator ensures that both conditions are met.

This approach allows you to use a single query and a conditional WHERE clause to filter results based on the value of a parameter. This is a more efficient and concise way to write such queries than using multiple queries or dynamic SQL.

The above is the detailed content of How to Implement a Conditional WHERE Clause in SQL Server Without Dynamic SQL?. For more information, please follow other related articles on the PHP Chinese website!