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
Go to Top of Page

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/
Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -