Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    36

    Unanswered: CASE statement in WHERE clause?

    I am trying to create a search query which takes 2 paramaters. The first is which column(@Criteria) to search on and the other is the search term(@SearchTerm).

    Here is my current code:
    Declare @Criteria nvarchar(100)
    Declare @SearchTerm nvarchar(100)

    Select Title, FirstName, LastName, [Name], IsCompany From Customer
    WHERE
    CASE
    WHEN @Criteria = 'FirstName' THEN (IsCompany = 0 AND FirstName LIKE '%' + @SearchTerm + '%')
    WHEN @Criteria = 'LastName' THEN (IsCompany = 0 AND LastName LIKE '%' + @SearchTerm + '%')
    WHEN @Criteria = 'CompanyName' THEN (IsCompany = 1 AND [Name] LIKE '%' + @SearchTerm + '%')
    END

    Is this type of query possible? I can't seem to make it work...
    I get the error " Incorrect syntax near '=' " which makes me think you can't use the CASE statement to complete the where clause of the query.
    Any clarification would be much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Posts
    36

    I see I see

    Thanks for the link, I come from a C# programming bkground hence the confusion. Do you know any other way of acheiving this single stored procedure approach to solving my problem or should I revert to IF ELSE statements with the individual queries for each @Criteria condition?
    Last edited by grooverinthesouth; 08-06-08 at 12:05.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can use a single stored procedure with IF/ELSE blocks to execute different queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oh gurus, whats wrong with this?

    Code:
    Select Title, FirstName, LastName, [Name], IsCompany 
    From Customer
    WHERE (@Criteria = 'FirstName' AND IsCompany = 0 AND FirstName LIKE '%' + @SearchTerm + '%')
    OR (@Criteria = 'LastName' AND IsCompany = 0 AND LastName LIKE '%' + @SearchTerm + '%')
    OR (@Criteria = 'CompanyName' AND IsCompany = 1 AND [Name] LIKE '%' + @SearchTerm + '%')
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Mar 2006
    Posts
    36
    Champion Thrasymachus.

    Thanks all for the quick feedback!

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by grooverinthesouth
    Champion Thrasymachus.
    sssshhhhhhh. edit that out. too many fragile egos around here.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    sssshhhhhhh. edit that out. too many fragile egos around here.
    Oh yeah - yet more passive aggressive put downs. That's it now - this is the last you'll see of me! **







    **: Surrogate Mr Winky
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by grooverinthesouth
    Champion Thrasymachus.
    Unfair. I did not know the contest had started.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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