sql server - How to use If Statement in Where Clause in SQL?

Question

I need to use if statement inside where clause in sql.

Select * from Customer
WHERE  (I.IsClose=@ISClose OR @ISClose is NULL)  
AND    
(C.FirstName like '%'+@ClientName+'%' or @ClientName is NULL )    
AND 
 if (@Value=2)
  begin
  (I.RecurringCharge=@Total  or @Total is NULL )    
  end
 else if(@Value=3)
begin
(I.RecurringCharge like '%'+cast(@Total as varchar(50))+'%' or @Total is NULL )  
end

Note:This is not the complete code.Everything is defined in SP.I Just written the code that was needed to understand the issue.

Thanks in advance.

Answer 1

SELECT *
  FROM Customer
 WHERE (I.IsClose=@ISClose OR @ISClose is NULL)  
   AND (C.FirstName like '%'+@ClientName+'%' or @ClientName is NULL )    
   AND (isnull(@Value,1) <> 2
        OR I.RecurringCharge = @Total
        OR @Total is NULL )    
   AND (isnull(@Value,2) <> 3
        OR I.RecurringCharge like '%'+cast(@Total as varchar(50))+'%'
        OR @Total is NULL )

Basically, your condition was

if (@Value=2)
   TEST FOR => (I.RecurringCharge=@Total  or @Total is NULL )    

flipped around,

AND (isnull(@Value,1) <> 2                -- A
        OR I.RecurringCharge = @Total    -- B
        OR @Total is NULL )              -- C

When (A) is true, i.e. @Value is not 2, [A or B or C] will become TRUE regardless of B and C results. B and C are in reality only checked when @Value = 2, which is the original intention.