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](http://stackoverflow.com/questions/10191424/case-in-where-sql-server) — Allan, May 13 '14 at 14:02

rory.ap · Answer 1 · 2014-05-13T14:06:10.637

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 '14 at 14:06

answered May 13 '14 at 13:58

rory.ap

34,009
10
83
174

Note, I've updated this because you need to enclose the whole AND statement in parens. – rory.ap May 13 '14 at 14:01

score 2 · Answer 2 · answered May 13 '14 at 13:59

2

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

answered May 13 '14 at 13:59

huMpty duMpty

14,346
14
60
99

@roryap you can also use `!=` (in `sql server`, I don't know about other db) – Bharadwaj May 13 '14 at 14:00
@roryap: Yes `!=` and `<>` are same – huMpty duMpty May 13 '14 at 14:01
Just checking. Didn't know that. – rory.ap May 13 '14 at 14:02
@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. – NoNaMe May 13 '14 at 14:12
Why don't you post what is condition3 is – huMpty duMpty May 13 '14 at 14:16
@zzzz -- I don't understand your question/statement. – rory.ap May 13 '14 at 14:29
@roryap Plz see the update question with running query – NoNaMe May 13 '14 at 14:33
@zzzz --"see the update question with running query" what are you talking about? I don't see anything related to my query in your update. – rory.ap May 13 '14 at 14:38
@roryap Please refresh the page and you will get the updated question – NoNaMe May 13 '14 at 14:39

score 0 · Accepted Answer · answered May 21 '14 at 03:38

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.

using if/case in sql server where clause

3 Answers3