How to use If Statement in Where Clause in SQL?

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.

  • How do i prevent Linq from adding a GUID so that SQL server can do it?
  • Stored Procedure, update consumed and date consumed logic
  • Check if role consists of particular user in DB?
  • List all foreign key constraints that refer to a particular column in a specific table
  • Math operation with dataGridView columns row by row then display final value in textBox
  • create script to change the collation of a database
  • Thanks in advance.

    4 Solutions collect form web for “How to use If Statement in Where Clause in SQL?”

    You have to use CASE Statement/Expression

    Select * from Customer
    WHERE  (I.IsClose=@ISClose OR @ISClose is NULL)  
    AND    
        (C.FirstName like '%'+@ClientName+'%' or @ClientName is NULL )    
    AND 
         CASE @Value
             WHEN 2 THEN (CASE I.RecurringCharge WHEN @Total or @Total is NULL) 
             WHEN 3 THEN (CASE WHEN I.RecurringCharge like 
                                   '%'+cast(@Total as varchar(50))+'%' 
                         or @Total is NULL )
         END
    
    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.

    Nto sure which RDBMS you are using, but if it is SQL Server you could look at rather using a CASE statement

    Evaluates a list of conditions and returns one of multiple possible
    result expressions.

    The CASE expression has two formats:

    The simple CASE expression compares an expression to a set of simple
    expressions to determine the result.

    The searched CASE expression evaluates a set of Boolean expressions to
    determine the result.

    Both formats support an optional ELSE argument.

    select * from xyz where (1=(CASE WHEN @AnnualFeeType = 'All' THEN 1 ELSE 0 END) OR AnnualFeeType = @AnnualFeeType)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.