Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2008
    Posts
    35

    Unanswered: Multiple where clause

    Hi

    Will someone help me with how to achieve the below:

    Pseudo code:

    Select *
    from table1
    where case when @a = 1 then
    <expression 1>, e.g. field1 = 'something'
    when @a = 2 then
    <expression 2>, e.g. field2 = 'something esle'
    when @a = 3 then
    <expression 3>
    ...
    ...
    ...
    end


    basically I want to execute a certain where clause depending on the value of @a.

    Any help will be much appreciated.


    Regards

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you willing to settle for terrible performance or are you willing to do a lot of extra typing?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2008
    Posts
    35
    I dont know .... explain please?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by getusama View Post
    I dont know .... explain please?
    please explain exactly what you're doing, i.e. what do table1, field1, field2 mean?

    and where do the "something" and "something esle" come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  Good, reliable performance
    
    IF 1 = @a
       SELECT *
          FROM foo
          WHERE bar = 'something'
    ELSE IF 2 = @a
       SELECT *
          FROM foo
          WHERE bar = 'something else'
    
    -- Easy to type
    
    SELECT *
       FROM foo
       WHERE (1 = @a AND bar = 'something')
          OR (2 = @a AND bar = 'someting else')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I agree with you Pat, except if you put that code in a sproc... The first time it is run a query plan will be created for the statement that gets hit. This will become the plan for the sproc and therefore will be sub-optimal (to say the least) for future runs using other parameter values.
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2008
    Posts
    35
    Thanks a alot Pat Phelan.

    I love your brilliant second idea. I have to use that because I have a massive statement and it would be difficult to manage it if I go with the first idea (which i wanted to avoid in the first place.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as you can live with the performance, the second method is very easy to type. It will usually run longer than running each of the groups within the WHERE clause sequentially, so it has been years since I've seen anyone try this in production. It urually works fairly well on very small data sets (under 1000 rows total), but performance becomes unbearable before you get very complex.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2008
    Posts
    35
    Hi Pat Phelan

    Will I be asking for too much if I were to ask to help me understand why you say the second approach will lack in performance?


    Thanks

  10. #10
    Join Date
    Jun 2008
    Posts
    35
    I meant to ask, as you have mentioned:

    "It will usually run longer than running each of the groups within the WHERE clause sequentially"

    Will it really make that big of a difference if I were to use say four clauses in the where clause using the second approach.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The introduction will answer your question. If you really want to understand things more deeply, read on.
    Dynamic Search Conditions in T-SQL (SQL 2005 and earlier)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2008
    Posts
    35
    Thanks for the link

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    I agree with you Pat, except if you put that code in a sproc... The first time it is run a query plan will be created for the statement that gets hit. This will become the plan for the sproc and therefore will be sub-optimal (to say the least) for future runs using other parameter values.
    So, for better performance create separate sprocs for each search, and call them conditionally from the main sproc. But that's more typing and admin, and you have duplicated logic which can be distasteful.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    the version is important for this. 2008 provides for some better plan generation\ caching options
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by gvee View Post
    I agree with you Pat, except if you put that code in a sproc... The first time it is run a query plan will be created for the statement that gets hit. This will become the plan for the sproc and therefore will be sub-optimal (to say the least) for future runs using other parameter values.
    Are you talking about the IF...ELSE approach? If you are then you should take a look at the "If ShowPlan Operator". Take a look at the estimated execution plan (not actual execution plan) for the following example:

    Code:
    declare @a int
    
    if(@a=1)
    begin
    	select 1
    end
    else if(@a=2)
    begin
    	select 2
    end
    else
    begin
    	select 3
    end
    As you can see SQL Server can take different paths depending on which branch in the IF...ELSE statements that gets hit.

Posting Permissions

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