Case inside where clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi friends,
I was trying to use case statement inside the where clause, which decides whether the condition should be used to filter the data based on the parameter.

i was trying the following:

Select * 
FROM FWB_SF_Opportunity B 
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth  
AND A.VCID = isnull(@VCID,A.VCID)  
WHERE case when @vcid <> '' then A.VCID = @VCID end

It gives me an following error:

<br />
Msg 102, Level 15, State 1, Line 35<br />
Incorrect syntax near '='.<br />

How can I put A.VCID = @VCID condition inside the case statement

Thanks in advance

Posted 4-Sep-12 0:54am

dhage.prashant01

Updated 4-Sep-12 1:06am

v2

Add a Solution

Comments

Sunil Kumar Pandab 4-Sep-12 7:03am

Are you using this code inside a procedure or not ?

dhage.prashant01 4-Sep-12 7:07am

yes it is inside procedure.

dhage.prashant01 4-Sep-12 7:11am

Condition:
When @VCID is not null then only the condition in the where clause should execute else it should not

any idea how to do it??

ssd_coolguy 4-Sep-12 7:12am

have you tried solution 2?

Sunil Kumar Pandab 4-Sep-12 7:14am

See my solution i have given below

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

prashant patil 4987 · Answer 1 · 2012-09-04T01:03:00

Solution 1

Try This:
This will work for you.

SQL

FROM FWB_SF_Opportunity B left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
WHERE A.VCID = (case when @vcid <> '' then @VCID end)

Good Luck.

Posted 4-Sep-12 1:03am

prashant patil 4987

Comments

ssd_coolguy 4-Sep-12 7:09am

i think you missed else part here...

prashant patil 4987 4-Sep-12 7:19am

this will also work..
WHERE A.VCID = (case when @vcid <> '' then @VCID else "Default value" end)

ssd_coolguy 4-Sep-12 7:23am

yes.. now it will work.. just improve your solution.. :)

dhage.prashant01 4-Sep-12 7:34am

Your solution is similar to
A.VCID = ISNULL(@VCID,A.VCID)

prashant patil 4987 4-Sep-12 7:42am

yes. same solution

ssd_coolguy · Answer 2 · 2012-09-04T01:07:00

Solution 2

hey try below query:-

SQL

declare @vcid int
set @vcid= ''  --for no filter
set @vcid= 100 -- for filter
select *
FROM FWB_SF_Opportunity B left join FWB_VWSFData A 
ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
WHERE @vcid =  case @vcid  when '' then @vcid else A.VCID  end

Posted 4-Sep-12 1:07am

ssd_coolguy

Updated 4-Sep-12 1:46am

v2

Comments

dhage.prashant01 4-Sep-12 7:32am

my condition is when @VCID = NULL then condition in where clause should get fire else it should not. Your solution don't work

ssd_coolguy 4-Sep-12 7:46am

see now. i improved my answer..

Sunil Kumar Pandab · Answer 3 · 2012-09-04T01:10:00

Solution 3

SQL

SELECT * FROM FWB_SF_Opportunity B left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
WHERE A.VCID = ISNULL(@VCID,A.VCID)

Try this one. It may help, I have not tested.

I have Another solution:- If your code is inside a procedure then before puting your code you can check whether @VCID is null or not null by the help of IF ELSE condition

Posted 4-Sep-12 1:10am

Sunil Kumar Pandab

Updated 4-Sep-12 1:13am

v2

Comments

dhage.prashant01 4-Sep-12 7:35am

Yes if else will do, it will repeat my entire code twice. which i want to avoid.

Aarti Meswania · Answer 4 · 2012-09-04T01:17:00

see this simple example

SQL

select a from
(
select 1 as a
union all
select 2 as a
) as b
where a = case when a>0 then 1 else 0 end

so, in where condition you should write

SQL

WHERE @vcid = case when @vcid <> '' then @VCID else null end
-- instead of 'Null' write else part

Happy Coding!
:)

Santhosh Kumar Jayaraman · Answer 5 · 2012-09-04T02:19:00

You can write it in simple way using OR keyword..

The below query will get you results where @vcid is null or is blank or it matches with column value. It might not what you asked but try this, it will work

SQL

Select *
FROM FWB_SF_Opportunity B
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
AND A.VCID = isnull(@VCID,A.VCID)
WHERE ( @vcid is null or @vcid ='' or A.VCID = @VCID )

If you still want in case statement, then try this

SQL

Select *
FROM FWB_SF_Opportunity B
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
AND A.VCID = isnull(@VCID,A.VCID)
WHERE A.VCID=case when @vcid <> '' then @VCID else null end

Case inside where clause

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0