How Can I Use a CASE Statement in a SQL WHERE Clause for Conditional Filtering?

Switch/Case statement of WHERE clause in SQL

Query statement:

The following query attempts to use the CASE statement in the WHERE clause to filter based on the values ​​of @locationType and @locationID:

<code class="language-sql">declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
CASE @locationType
    WHEN 'location' THEN account_location = @locationID
    WHEN 'area' THEN xxx_location_area = @locationID
    WHEN 'division' THEN xxx_location_division = @locationID
END;</code>

Error:

This query results in a syntax error because the CASE expression expects a single return value. Various WHEN clauses incorrectly included an equality comparison (= @locationID) that was not a CASE expression.

Solution:

The correct syntax for using a CASE statement in a WHERE clause is to assign the expression to the target column:

<code class="language-sql">declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
@locationID = 
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area
      WHEN 'division' THEN xxx_location_division
  END;</code>

In this modified query, the CASE expression returns the corresponding location identifier based on the value of @locationType. The WHERE clause then compares this value with @locationID to filter the results as expected.

The above is the detailed content of How Can I Use a CASE Statement in a SQL WHERE Clause for Conditional Filtering?. For more information, please follow other related articles on the PHP Chinese website!