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 |
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-09-16 : 04:16:42
|
| I have a column in a table called Policed that has either Y or N. I also have a variable called @Policed which could be Y, N or All. How do I best do a where clause.If @Policed = Y Then return all rows where Policed = YIf @Policed = N Then return all rows where Policed = NIf @Policed = All then return all rows.Select PolicedFrom TableWhere Policed = Can this be done with;AND Policed = COALESCE(@Policed, 'Y', @Policed, 'N', '?', 'All') |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-09-16 : 10:28:35
|
Possible but not tested:SELECT Policed FROM Table WHERE Polied LIKE CASE Policed WHEN 'Y' THEN 'Y' WHEN 'N' THEN 'N' ELSE '%' djj |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-18 : 08:54:48
|
| Depending on your situation, you might find it better to use 3 queries. Assuming you have a query from some client app that provides the @policed value as a parameter then you might find it better to just call a direct query with the value or absence of value hard coded. If your data is skewed then you'll get a plan that does a table scan or index lookup depending on your first invocation and that plan could be totally wrong for the other cases. So with a few values like this it can pay off to just invoke different queries/SPs based on what you know. WITH RECOMPILE is also an option but not ideal for only a few values. Any of the previous solutions may work out for you as well - just watch out for what I have described and consider the simple path I have suggested. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-09-18 : 11:02:50
|
| Where (@Policed = 'All' OR Policed = @Policed) |
|
|
|
|
|
|
Where clause