Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2008
    Posts
    35

    Unanswered: How to say something is equal to anything

    Hi

    I have a select statement like below which returns results according to what I feed in conditon1.

    In most cases I would feed a certain valid value (from a table) but I now have a situaltion where I would need to ignore condition1 so that it could be equal to anything.

    Code:
    declare var varchar(20)
    ...
    set var = 'anything'
    ...
    Select field1, field2
    from table 
    where condition1 = var
    Any help is appreciated.


    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This covers every possible option:
    Dynamic Search Conditions in T-SQL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by getusama View Post
    ... I would need to ignore condition1 so that it could be equal to anything.
    this may come as a surprise to you but the best way to do that (and most efficient) is to omit the condition
    Code:
    Select field1, field2
    from table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2008
    Posts
    35
    Quote Originally Posted by r937 View Post
    this may come as a surprise to you but the best way to do that (and most efficient) is to omit the condition
    Code:
    Select field1, field2
    from table
    Yea, that is exactly what I would like but I would need to use the expression:

    where condition1 = var

    so that other scenarios can be handled.

    Also would like to thank 'pootle flump' but that is quite an extensive articel which I have to go through at a later time but for now I have to think of something else.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is this SQL Server???

    In any case

    ANSI

    Col1 = COALESCE(@var,Col1)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by getusama View Post
    ...but I would need to use the expression:
    no you wouldn't

    just omit the condition altogether

    what, you can't use an IF statement in T-SQL ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2008
    Posts
    35
    Quote Originally Posted by Brett Kaiser View Post
    Is this SQL Server???

    In any case

    ANSI

    Col1 = COALESCE(@var,Col1)
    Thank you so much 'Brett Kaiser' that is exactly what I am looking for.

    I have actually come back to share my idea of what I have done (if it may interest you) but I like your idea much more because it is more optimised and short. Here is what I was going to do:


    set @var = 'IgnoreME' -- if I wanat to ignore the condtion
    ...

    where (@varchar = 'IgnoreMe')
    or (@varchar <> 'IgnoreMe' and col1 = @varchar)

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser View Post
    Col1 = COALESCE(@var,Col1)
    Scannity scan.

    OP - what version of SQL Server are you using?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy's solution would read something like:
    Code:
    IF @search_con = 'Anything' THEN
    SELECT *
    FROM myTable
    ELSE
    SELECT *
    FROM myTable
    WHERE column = @search_con
    It is logically the same as Brett's solution (apart from handling NULLs) but better optimised.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2008
    Posts
    35
    I am usingSQL Server 2005.

    Sorry but who is rudy?

    So, pootle flump - you too think that Brett Kaiser's solution is better optimized.
    Last edited by getusama; 10-04-10 at 11:25.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Rudy's (r937)/Pootle's is theoretically better optimized because calling COALESCE() in your WHERE clause will cause you to lose the ability to use indexes against that column.
    Last edited by Teddy; 10-04-10 at 12:13.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by getusama View Post
    Sorry but who is rudy?
    the guy in post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2008
    Posts
    35
    Quote Originally Posted by Teddy View Post
    Rudy's/Pootle's (r937) is theoretically better optimized because calling COALESCE() in your WHERE clause will cause you to lose the ability to use indexes against that column.
    Oh, okay and how about my one:

    set @var = 'IgnoreME' -- if I wanat to ignore the condtion
    ...

    where (@varchar = 'IgnoreMe')
    or (@varchar <> 'IgnoreMe' and col1 = @varchar)

    I know it is less optimised than Rudy's but I would not need to write my select statements twice here (which I want to avoid so that I can manage my script better).

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You know, I suddenly had a sense of deja vu.
    http://www.dbforums.com/microsoft-sq...re-clause.html

    Much of what we discussed in the last thread applies here. Also, your idea will result in the same performance issue as using COALESCE(). Before SQL Server 2008 CU 5 the trade off is:
    lots of typing Vs (potentially) poor performance
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I also think that since this is your second post on this subject it is probably time to take 20 minutes out and read the link. Everything we are covering is in there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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