-
Feb 28th, 2012, 12:50 PM
#1
Thread Starter
PowerPoster
[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.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 28th, 2012, 12:54 PM
#2
Re: sql server 2008 - syntax for conditional case in where clause
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)
)
-
Feb 28th, 2012, 01:38 PM
#3
Thread Starter
PowerPoster
Re: sql server 2008 - syntax for conditional case in where clause
Thanks! (You've missed me, haven't you?)
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 28th, 2012, 01:42 PM
#4
Re: [RESOLVED] sql server 2008 - syntax for conditional case in where clause
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
-
Feb 28th, 2012, 01:51 PM
#5
Thread Starter
PowerPoster
Re: [RESOLVED] sql server 2008 - syntax for conditional case in where clause
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.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 28th, 2012, 02:50 PM
#6
Re: [RESOLVED] sql server 2008 - syntax for conditional case in where clause
@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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|