Dynamic Operator in sql case statement

Question

0.00/5 (No votes)

See more:

hi,
How I use operator in case statement,please see bellow example.

SQL

CreatedByClient =
case @AddedBy
when 0 then  CreatedByClient
when 1 then 0
when 2 then >0
end

I write a case based on @AddedBy ,I am try to when @AddedBy value is 2 then CreatedByClient>0 how do I do this.please help.

Update

Hi ,thanks to all for reply,please see bellow

SQL

for query I pass 0,1,2,for 0 return all,for 1 return only 0 and for 2 return only non 0 rows.

please assists me how do I do this .

Posted 4-Jan-15 21:43pm

KuntalBose

Updated 4-Jan-15 22:40pm

v2

Add a Solution

Comments

Jitendra Ku. Sahoo 5-Jan-15 3:51am

You can have a look here http://msdn.microsoft.com/en-IN/library/ms181765.aspx

KuntalBose 5-Jan-15 3:55am

Hi,thanks
I see the link,but not see a example how to set operator in then for my situation
when 2 then >0

CHill60 5-Jan-15 3:58am

You can't set a value to '>0' try setting it to 1 instead. What are you actually trying to achieve?

2 solutions

Solution 1

There are infinite values larger than 0 (zero) - as there are infinite values smaller than zero...So your basic problem is that you try to assign a larger-than-zero value to your variable, but didn't decided which one of them!
CreatedByClient = >0 is a logic error with and without the case in-between...
So you have to pick a value and assign it...

Posted 4-Jan-15 22:00pm

Kornfeld Eliyahu Peter

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)

jaket-cp · Accepted Answer · 2015-01-05T01:32:00

If I understand your criteria correctly, depending on what the value of @AddedBy is you want different record/results.
You could try something like this:

SQL

--setup test data
declare @TabNum table(id int identity(1,1), num int);
insert into @TabNum
	select 987 num
	union all select 458
	union all select 0
	union all select 45
	union all select 0
	union all select 0
	union all select 0
	union all select 58
	union all select 569
	union all select 2 
;

--test @AddedBy with different values 0, 1 and 2
declare @AddedBy integer;
set @AddedBy = 0;
--set @AddedBy = 1;
--set @AddedBy = 2;

select * from @TabNum
--for query I pass 0,1,2,
where (@AddedBy <> 0 or num >= 0) --for 0 return all,
and (@AddedBy <> 1 or num = 0) --for 1 return only 0 and 
and (@AddedBy <> 2 or num <> 0) --for 2 return only non 0 rows.
;