CODEWITHSUNDEEP
sqlsql-servert-sql
Adam91Holt
Adam91Holt

Reputation: 1058

Only use a where clause when bit = 1

I have some SQL code that looks like this at the moment.

    insert into @SortedProducts (peid)
    select pd.ProductEntryId 
    from @ProductFilters FILT
        INNER JOIN J_ProductDetails pd ON FILT.ProductEntryId = pd.ProductEntryId
    WHERE pd.Active = 'True'
    and pd.IsRangeOfProducts = CASE WHEN @ClearanceFeatureApplied = 1 then 0 else 1 END

However, on the last line, I only want to apply that where clause if @ClearanceFeatureApplied = 1

If @ClearanceFeatureApplied = 0 then I should not have this where clause. pd.IsRangeOfProducts is also a bit.

Any advice would be greatly appreciated.

Thanks!

Upvotes: 2

Views: 180

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Just use boolean logic:

insert into @SortedProducts (peid)
    select pd.ProductEntryId 
    from @ProductFilters FILT join
         J_ProductDetails pd 
         on FILT.ProductEntryId = pd.ProductEntryId
    where pd.Active = 'True' and
          (@ClearanceFeatureApplied <> 1  or pd.IsRangeOfProducts = 0) ;

Upvotes: 6

Moe Sisko
Moe Sisko

Reputation: 12005

If I've understood this correctly, then maybe something like:

WHERE pd.Active = 'True'
    and 1 = CASE WHEN @ClearanceFeatureApplied = 0 THEN 1 
                 WHEN pd.IsRangeOfProducts = 0 THEN 1  -- XX
                 ELSE 0 END

Above assumes @ClearanceFeatureApplied will not be null. If you get to line XX, then @ClearanceFeatureApplied will always be 1, so I optimized the condition: 

 and pd.IsRangeOfProducts = CASE WHEN @ClearanceFeatureApplied = 1 then 0 else 1 END

to: 

  pd.IsRangeOfProducts = 0

Upvotes: 0

rayzinnz
rayzinnz

Reputation: 1948

You can add an OR to the where clause (surrounded by brackets) to ignore this section when @ClearanceFeatureApplied = 0:

insert into @SortedProducts (peid)
select pd.ProductEntryId 
from @ProductFilters FILT
    INNER JOIN J_ProductDetails pd ON FILT.ProductEntryId = pd.ProductEntryId
WHERE pd.Active = 'True'
and (@ClearanceFeatureApplied = 0 OR pd.IsRangeOfProducts = CASE WHEN @ClearanceFeatureApplied = 1 then 0 else 1 END)

If you have nulls in your bit columns, you will need to componsate for that.

Upvotes: 2

Related Questions