using if/case in sql server where clause

Question

I'm trying to add a column in where clause in SQL Server 2005, but issue is I have to add it for a given condition like

where condition  1
and condition 2
if(@val !=null and @val = 'test')
begin
and condition 3
end

but when I try this SQL Server gives me error:

Incorrect syntax near the keyword 'and'. Severity 15 Procedure Name
Incorrect syntax near 'END'. Severity 15 Procedure Name

I did some R&D and found that I should use case statement instead and then I tried this

where condition  1
    and condition 2
    CASE @val 
    WHEN 'test' THEN AND condition 3
END

but now it's giving error:

Incorrect syntax near the keyword 'CASE'. Severity 15 Procedure Name

Can any one help me to sort out the problem? Thanks.

EDIT

select *
from Tables with joins
where ac_jd_date_closed >= convert(VARCHAR(10),dateadd(dd, -@period, getdate()), 101)
and ac_jd_date_closed <= convert(VARCHAR(10),dateadd(dd, 0, getdate()), 101) 
and lo_pc_last_name not in ('Shroder')
and lm_ap_pr_id NOT IN (4743, 2683)

I want to add this condition when spm David otherwise this condition should not be there.

possible duplicate of CASE in WHERE, SQL Server

Allan
– Allan

2014-05-13 14:02:13 +00:00
Commented May 13, 2014 at 14:02 — Allan
– Allan, Commented May 13, 2014 at 14:02

rory.ap · Accepted Answer · 2014-05-13 14:06:10Z

3

You can remove the CASE statement and substitute AND (@Val<> 'test' OR (@Val = 'test' AND condition 3)):

WHERE <condition  1>
AND <condition 2>
AND (@Val<> 'test' OR (@Val = 'test' AND <condition 3>))

edited May 13, 2014 at 14:06

answered May 13, 2014 at 13:58

rory.ap

35.7k15 gold badges104 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rory.ap Over a year ago

Note, I've updated this because you need to enclose the whole AND statement in parens.

huMpty duMpty · Accepted Answer · 2014-05-13 13:59:23Z

2

where condition  1
and condition 2
and ((@val='test' and condition 3) Or (@val!='test'))

answered May 13, 2014 at 13:59

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

9 Comments

Bharadwaj Over a year ago

@roryap you can also use != (in sql server, I don't know about other db)

huMpty duMpty Over a year ago

@roryap: Yes != and <> are same

rory.ap Over a year ago

Just checking. Didn't know that.

NoNaMe Over a year ago

@roryap i just want to clear one thing from any of you both that in my case condition 3 will not work as a statement, because it is conditional. just clear me if both answers works as per my requirement.

huMpty duMpty Over a year ago

Why don't you post what is condition3 is

|

NoNaMe · Accepted Answer · 2014-05-21 03:38:45Z

Thanks for all to answer the question, but I used a little trick to solve the problem in this way

if(@spm='David')
begin

select *
    from Tables with joins
    where ac_jd_date_closed >= convert(VARCHAR(10),dateadd(dd, -@period, getdate()), 101)
    and ac_jd_date_closed <= convert(VARCHAR(10),dateadd(dd, 0, getdate()), 101) 
    and lo_pc_last_name not in ('Shroder')
    and lm_ap_pr_id NOT IN (4743, 2683) 
end  
else 
begin 
select *
    from Tables with joins
    where ac_jd_date_closed >= convert(VARCHAR(10),dateadd(dd, -@period, getdate()), 101)
    and ac_jd_date_closed <= convert(VARCHAR(10),dateadd(dd, 0, getdate()), 101) 
    and lo_pc_last_name not in ('Shroder')
end

means i used the same full query with condition, hope this trick will help someone to solve his issue.

Collectives™ on Stack Overflow

using if/case in sql server where clause

3 Answers 3

1 Comment

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related