SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case in Where Clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
286 Posts

Posted - 09/25/2013 :  15:15:52  Show Profile  Visit Zath's Homepage  Reply with Quote

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

USA
286 Posts

Posted - 09/25/2013 :  15:17:48  Show Profile  Visit Zath's Homepage  Reply with Quote
The second AND should be an OR.

It's getting late :D
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 09/25/2013 :  15:18:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37157 Posts

Posted - 09/25/2013 :  15:18:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
337 Posts

Posted - 09/26/2013 :  09:40:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000