CODEWITHSUNDEEP
sqlsql-server
NoNaMe
NoNaMe

Reputation: 6222

using if/case in sql server where clause

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.

Upvotes: 0

Views: 142

Answers (3)

NoNaMe
NoNaMe

Reputation: 6222

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.

Upvotes: 0

rory.ap
rory.ap

Reputation: 35260

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>))

Upvotes: 3

huMpty duMpty
huMpty duMpty

Reputation: 14460

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

Upvotes: 2

Related Questions