Case in Where Clause - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Case in Where Clause

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-09-25 : 15:15:52

There is a parameter coming into a stored procedure
called @Status

SELECT * FROM myTable
WHERE someField = 55

AND	Status = CASE @Status WHEN -1 THEN 0 END 
AND	Status = CASE @Status WHEN -1 THEN 1 END

The trouble is if I comment out one of the AND statements, the other AND works, and vise versa.
But if they are both being executed, then no records.
I am expecting about 2000 records.

Any suggestions?

Thanks

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-09-25 : 15:17:48

The second AND should be an OR.

It's getting late :D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-25 : 15:18:26

Do you want OR instead of AND?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-25 : 15:18:50

You beat me to it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-26 : 09:40:30

Try - notice the parenthesis


SELECT * FROM myTable
WHERE someField = 55
AND (Status = CASE @Status WHEN -1 THEN 0 END 
  OR Status = CASE @Status WHEN -1 THEN 1 END)

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources