April 23, 2012 at 9:16 pm
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?
April 23, 2012 at 10:49 pm
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 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]
April 24, 2012 at 2:26 am
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.
April 24, 2012 at 2:31 am
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?
April 24, 2012 at 2:43 am
April 24, 2012 at 6:40 am
the op might also want to do the following
WHERE CASE WHEN Bla is true then foo > bar ELSE bar < foo END
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]
April 24, 2012 at 11:05 am
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.
April 24, 2012 at 10:18 pm
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. 😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply