SQL WHERE clause - Conditional filters

Question

I have a query I want to work with the following logic (knowing full well it won't work as written):

select *
From orders
Where   if Value1 != 'TEST'
        (Value1 = @1)
        Else if Value2 != 'TEST'
        (Value2 = @2)
        Else 1=2

I don't think I can use a Case...When or iif since the value to filter the query on changes based on the outcome of condition. Any ideas?

Can you use a stored procedure, that can be done with a dynamic sproc — logixologist, Nov 18 '15 at 20:42
sorry for not being descriptive enough! Using microsoft SQL server 2012, so T-SQL — user3009908, Nov 18 '15 at 20:42
Cant use and/or. If the first is true, I don't want to match on the second. If the first doesn't have good data, want to use the second only — user3009908, Nov 18 '15 at 20:43
Sorry, comments coming faster than I can respond, so thanks! unfortunately I'll have to plug this into some software where I cant reference stored procs... its been frustrating me — user3009908, Nov 18 '15 at 20:44
please search. this is a very commonly asked question. here is one answer from a basic search. http://stackoverflow.com/questions/1978179/how-to-conditionally-filter-on-a-column-in-a-where-clause — Jeremy, Nov 18 '15 at 20:44
That link doesn't help... in that one, like the others that I found, have the luxury of comparing two items to a single variable. What I'm looking for is: IF variable1 != test1 then variable1 = parameter1 ELSE If Variable2 != test2 then Variable2 = parameter2 — user3009908, Nov 18 '15 at 20:49
Essentially, i feel like I need two where clauses, but only want to use one at a time depending on the data presesnt — user3009908, Nov 18 '15 at 20:52

score 0 · Accepted Answer · answered Nov 18 '15 at 20:47

0

Your logic is equivalent to:

Select *
From orders
Where (Value1 <> 'TEST' and Value1 = @1) OR
      (Value1 = 'TEST' and Value2 <> 'TEST' and Value2 = @2)

This is not 100% equivalent. If Value1 could be NULL, then the logic should take that into account:

Select o.*
From orders o
Where (Value1 <> 'TEST' and Value1 = @1) OR
      (COALESCE(Value1, 'TEST') = 'Test' and Value2 <> 'TEST' and Value2 = @2)

answered Nov 18 '15 at 20:47

Gordon Linoff

OK, you just made me feel stupid - I can't believe I didn't think of that! Thanks! – user3009908 Nov 18 '15 at 21:35

1 Answers1