[RESOLVED] sql server 2008 - syntax for conditional case in where clause

If my input parameter is SearchType = 'EXACT' then I want an exact match of the other parameters otherwise I want a match of substrings.

So it goes like this, where I want only one of the last two conditions:

Code:

---

        WHERE  ((@PracticeID IS NULL) OR (tblPendingPractices.PracticeID = @PracticeID))
        AND ((@PracticeFaxNumber IS NULL) OR (tblPendingPracticeLocations.Fax = @PracticeFaxNumber))
       
        AND ((@PracticeName IS NULL) OR (tblPendingPractices.PracticeName LIKE '%' + @PracticeName + '%')
       
        AND ((@PracticeName IS NULL) OR (tblPendingPractices.PracticeName = @PracticeName))

---

How do I execute either one of the two matches to PracticeName depending on SearchType's value? It's something like this, but this isn't valid sql:

Code:

---

        case
                when @SearchType = 'EXACT'
                then AND ((@PracticeName IS NULL) OR (tblPendingPractices.PracticeName = @PracticeName))
                else AND ((@PracticeName IS NULL) OR (tblPendingPractices.PracticeName LIKE '%' + @PracticeName + '%'))
        end

---

or

Code:

---

        AND ((@PracticeName IS NULL) OR
                case
                        when @SearchType = 'EXACT'
                        then (tblPendingPractices.PracticeName LIKE '%' + @PracticeName + '%')
                        else (tblPendingPractices.PracticeName = @PracticeName)
                end

---

Every example I find has the equals operator outside of the case but I don't know if I want equals or LIKE until I'm inside the case. :confused:

CASE doesn't work that way - it's not a logic flow control statement like in a higher language. It's really just a glorified ISNULL/COALESCE "function" - returns a single value - not flow control.

You have to do

Code:

---

Where (@SearchType = 'EXACT'
              and (this and that...
                            and more and more)
 ) or (@SearchType <> 'EXACT'
              and (this and that...
                            and more and more)
 )

---

Thanks! (You've missed me, haven't you?)

or you just need to be a little creative... here's how I've done it...

Code:

---

where
  case
    when someCondition then 1
    when someotherCondition then 1
    when yetAnotherCondition then 1
    else 0
  end = 1

---

-tg

tg - I've seen many examples like yours, but what I couldn't find the syntax for is exactly how to code a LIKE match for one condition and an exact/equals match for the other condition.

@mock - tg is taking a WHERE clause that is normally evaulating TRUE / FALSE'ness with all the usual AND / OR and NOT keywords and putting a CASE expression within. CASE has to return a value - so he's saying

Where CASE...END = 1

And then making sure to put THEN 1 is all the places he ways "TRUE" to come back out.

Although this is not in and of itself a bad idea - and I'm sure I've used it myself several times - it does have some pitfalls.

A CASE statement without an ELSE can return a NULL - if none of the conditions prior match. That NULL might have different behavior in an

END = 1

evaluation then it would in a bunch of OR (... and ...) conditions.

Also - there are all kinds of query optimization passes made on queries so that execution plans can be determined. AND / OR and NOT's with ()'s are standard fare - used by coders of SQL since 1950's.

Those optimizers are going to hit a brick wall "trying" to "peer" into the WHEN's of a CASE statement - probably not going to consider them at all. Thus you lose out on any potential benefits that might have happened.