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 have just written the code that was needed to understand the issue.

RichardTheKiwi · Answer 1 · 2012-11-01T06:08:02.280

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.

Vishal Suthar · Accepted Answer · 2012-11-01T06:25:03.183

6

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

edited Nov 01 '12 at 06:25

answered Nov 01 '12 at 06:04

Vishal Suthar

17,013
3
59
105

i tried same but i gave me error "Incorrect syntax near '='" on this line when 1 then (I.RecurringCharge=@Total or @Total is NULL ) – Asp_Newbie Nov 01 '12 at 06:14
Yeah it is giving the error at same location .i am already executing it in my original SP. – Asp_Newbie Nov 01 '12 at 06:23
2

Again editted the code..Yes..because `I.RecurringCharge=@Total` is also an expression which should also be evaluated with `CASE WHEN` statement...@Asp_Newbie – Vishal Suthar Nov 01 '12 at 06:30

score 2 · Answer 3 · answered Nov 01 '12 at 06:02

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.

Then rather look at using a CASE statement – Adriaan Stander Nov 01 '12 at 06:04 — Adriaan Stander, Nov 01 '12 at 06:04

score 1 · Answer 4 · edited Jun 14 '17 at 12:01

1

select * from xyz where (1=(CASE WHEN @AnnualFeeType = 'All' THEN 1 ELSE 0 END) OR AnnualFeeType = @AnnualFeeType)

edited Jun 14 '17 at 12:01

Eric Aya

69,473
35
181
253

answered Jun 14 '17 at 11:57

Mitesh Vora

458
8
21

How to use If Statement in Where Clause in SQL?

4 Answers4

Linked