In SQL Server, the CASE statement in the WHERE clause is a powerful tool that allows you to apply conditional logic to filter rows based on specified conditions. The CASE statement evaluates one or more conditions and returns a result based on the first condition that is true.

Syntax

Here's a basic structure of a CASE statement in the WHERE clause:

SELECT column1, column2, ...
FROM your_table
WHERE
CASE
WHEN condition1 THEN 1
WHEN condition2 THEN 2
...
ELSE 0
END = your_expression;

Let's break down the components:

CASE: This keyword begins the CASE statement.

WHEN condition THEN result: This part specifies the conditions to be evaluated. If a condition is true, the corresponding result is returned.

ELSE result: This part provides a default result if none of the specified conditions are true.

END: This keyword marks the end of the CASE statement.

Example

Here's a practical example to illustrate the usage of a CASE statement in the WHERE clause:

SELECT product_name, unit_price
FROM products
WHERE
CASE
WHEN category_id = 1 THEN 1
WHEN category_id = 2 AND stock_quantity > 10 THEN 1
ELSE 0
END = 1;

In this example, the query retrieves product names and unit prices from the "products" table. It includes products that belong to category 1 or category 2 with a stock quantity greater than 10. The CASE statement evaluates these conditions and returns 1 if the conditions are met and 0 otherwise.

It's essential to note that while the CASE statement in the WHERE clause can be useful, it may impact performance, especially in large datasets. In some cases, alternative methods like using boolean logic with AND and OR operators might be more efficient. Always consider the specific requirements of your query and the performance implications of different approaches.