Another CASE statement problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Another CASE statement problem

Author

Topic

Lehnis
Starting Member

2 Posts

Posted - 2007-08-14 : 02:38:14

I've been looking for an answer to this question but haven't been able to find one.

The statement looks like this:

WHERE table1.number = CASE @variable
WHEN 1 THEN table1.number --All the rows
WHEN 0 THEN 1 --All rows where table1.number is 1
WHEN 2 THEN ??? Here I want all the rows where table1.number isn't 1
END

I've tried 'BETWEEN 2 AND 1000000' and '> 1' and other crazy solutions but nothing works.

Ideas anyone?
Thanks!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-08-14 : 03:03:19

Hi,
give it a try...
WHERE table1.number between CASE @variable
WHEN 1 THEN table1.number --All the rows
WHEN 0 THEN 1 --All rows where table1.number is 1
WHEN 2 THEN 2
END
and
CASE @variable
WHEN 1 THEN table1.number --All the rows
WHEN 0 THEN 1 --All rows where table1.number is 1
WHEN 2 THEN 1000000
end

Lehnis
Starting Member

2 Posts

Posted - 2007-08-14 : 03:13:34

OMG it worked, thank you, shallu1_gupta, SO MUCH! Made my day a lot easier. Must admit that it look obvious now when I look at it a second time...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources