Using two = signs in CASE statement?

Question

I'm attempting to use a case statement, dependent on a variable, to determine part of my WHERE clause, but it doesn't like what I'm doing. Here's my query (modified for simplicity)

DECLARE @SalesType VARCHAR(10)

SET @SalesType = 'Bulk'

SELECT CASE 
        WHEN fwsf.Customer_Cardlock_Customer = 'True'
            THEN 'CFN'
        ELSE 'Bulk'
        END AS 'Prod Type'
FROM TABLE t
WHERE CASE 
        WHEN @SalesType = 'Bulk' then t.customer_type = 'False'
        WHEN @SalesType = 'CFN' then t.customer_type = 'True'
        End

Put another way, I want to state in the WHERE clause that when the @SalesType is a given value, to select the rows that have another value.

EDIT:For sake of others, I realized I had another scenario where I may need to select an 'All' option. Per Shawn below, he corrected my original proposed solution with the following that I verified works:

AND t.customer_type =
CASE @SalesType
    WHEN 'Bulk' then 'False'
    WHEN 'CFN'  then 'True'
    WHEN 'All'  then t.customer_type
END
        END

Answer 1

Your summary is very good and this is a common way to translate or decode values. Per your edit there's also a case where you want all results.

WHERE t.customer_type =
    CASE @SalesType
        WHEN 'Bulk' then 'False'
        WHEN 'CFN'  then 'True'
        WHEN 'All'  then t.customer_type
    END

I've modified the expression to use a different form of case. You might find this to be more readable and at least now you know it exists.

I'll also note that in your original logic, if the case falls through then the result will be null.

The "all rows" can be handled by just returning the customer_type value which is naturally always equal to itself, except when null. To handle null you'd need to do something a bit more complicated like coalesce(t.customer_type, 'ALL!') on both sides of the equality.

If optimization is a concern you might be better off with regular and/or logic.

(
    @SalesType = 'Bulk' AND t.customer_type = 'False' OR
    @SalesType = 'CFN'  AND t.customer_type = 'True'  OR
    @SalesType = 'All'
)