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.
| Author |
Topic |
|
trouble2
Constraint Violating Yak Guru
267 Posts |
 Posted - 2009-11-06 : 08:30:09
|
| Hi, I know how to make a sp with a bit to select on a field for only true, but I actually need something which does@Filter = -1 - show all@Filter = 0 - show only false for a specific field@Filter = 1 - show only true for a specific fieldSo what is the smartest way to do this? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-06 : 08:38:20
|
| WHERE Filter = @filter or (@filter = -1 and filter >= 0)JimEveryday I learn something that somebody else already knew |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-06 : 08:40:25
|
| Not sure about "smartest" but you could make the parameter a nullable bit, so NULL is show all. Implementing that depends on your model but assuming you have a non-nullable true/false bit in the table here's one way:where (@filter is null OR trueFalse = @filter)Be One with the OptimizerTG |
|
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-11-06 : 08:49:50
|
| It actually needs to be checking for a varchar 'True' or 'False'So something like when Filter='true'The secret to creativity is knowing how to hide your sources. (Einstein) |
|
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-11-06 : 09:01:27
|
| So when I say:@Filter = -1, it does not matter@Filter = 0, return only records with 'false' values@Filter = 1, return only records with 'true' valuesThe secret to creativity is knowing how to hide your sources. (Einstein) |
|
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-11-06 : 09:02:22
|
| Perhaps I should use a case statement ? |
|
|
|
|
|
|
Only true, only false or all