Column = anything

  • I have a query in which a column may or may not be compared to a value in the WHERE clause.

    I know how to do this using dynamic sql. But, I would like to be able to compile this query. Is there any way to code "WHERE column = anything" without using dynamic sql?

  • EdwinGene (4/23/2012)


    I have a query in which a column may or may not be compared to a value in the WHERE clause.

    I know how to do this using dynamic sql. But, I would like to be able to compile this query. Is there any way to code "WHERE column = anything" without using dynamic sql?

    you might need to read a blog by gail on catch all queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ can you post the query you are trying to run and the DDL and Sample data for the tables involved? if you need help on the proper way to do this please see the link in my signature. thanks


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • You need to be more precise about what you are trying to do. From what I understand this is the only way.

    Declare @temp int = 11

    Select * From Test Where Id = @temp

    There could be more ways if you elaborate on what exactly the business requirement here is.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Is there any way to code "WHERE column = anything" without using dynamic sql?

    Yes it is possible:

    SELECT * FROM Table WHERE Column = Column OR Column IS NULL

    I'm sure you want something else, don't you?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Yea. the OP is not elaborate enough for us to know what the requirement actually is

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • the op might also want to do the following

    WHERE CASE WHEN Bla is true then foo > bar ELSE bar < foo END


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I know that when I write report queries, I often use:

    SELECT COL1, COL2, COL_W_CRITERIA WHERE COL_W_CRITERIA = @criteriaparam OR @criteriaparam = 'all'

    Then if the user enters 'all' for the criteria parameter the query pretty much returns everything.

  • Yes. Seems like the only way to do "WHERE column = anything" is using a temporary variable ie: "WHERE column = @anything".

    Can't think of anything else until the OP clarifies the requirement. 😎

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply