Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [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.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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)
     )

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width