SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional Statements in WHERE Clauses


Conditional Statements in WHERE Clauses

Author
Message
Paul Paiva
Paul Paiva
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 265
I had 1 or 2 approaches to this, but now I have a whole set of them.



This is such a textbook example of what a technical blog is all about! So great to see so many different solutions to one problem! All contributors are doing a great service to the community, regardless of your views!



Paul

- Paul

http://paulpaivasql.blogspot.com/
Sotiris Filippidis
Sotiris Filippidis
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 9
Wow. It's been 3 years and still there is a lot of activity on this post. Thank you all very much for your replies.







Adding to what I said then, the conditional approach is probably not suitable for large data sets or systems where response is critical. Smart joins, dynamic SQL or even application - level constructed query statements would probably do the job better there. It's better to think of this specific approach as flexibility vs performance. You can choose what you need more.







What you get with this approach is a flexible WHERE clause generator - without the generator Smile Of course it's always possible to construct such a WHERE clause in other ways such as those mentioned above, but, in my opinion, it'd take more man-hours and it should only be done where it's worth it.

Sotiris L. Filippidis
the sqlist
the sqlist
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 739
To be honest I didn't understand this article. First of all the example doesn't cover the first condition "All records that contain the word "the" in the company name", but it's fine because no german company has that in name, irrelevant however. To me this is the way to go:



select

customerid, companyname, country

from

customers

where

--companyname LIKE '%the%'

--AND

companyname NOT LIKE 'A%'

AND

country='Germany'



Plain and simple.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
Sotiris Filippidis
Sotiris Filippidis
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 9
The two conditions in the article were just examples of the wealth of criteria there are for the same query. They were two separate conditions which were not meant to be combined, and of course the example's purpose was not to demonstrate those specific conditions.







Just suppose those two conditions could be part of a larger array of conditions in a reporting UI, for example, and would apply depending on user's choices. Of course, the letter "A" or the string "the" would be parameters the user could specify in some kind of text box or something. In that case, you would have to use some conditional logic or dynamic SQL and not the plain SQL statement you typed. Otherwise, you'd have to have one different statement for every condition. Smile

Sotiris L. Filippidis
the sqlist
the sqlist
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 739
Sotiris Filippidis (10/10/2007)
Just suppose those two conditions could be part of a larger array of conditions in a reporting UI, for example, and would apply depending on user's choices. Of course, the letter "A" or the string "the" would be parameters the user could specify in some kind of text box or something. In that case, you would have to use some conditional logic or dynamic SQL and not the plain SQL statement you typed. Otherwise, you'd have to have one different statement for every condition. Smile




Maybe the example chosen was not the best. However you don't need a dynamic SQL to solve the problem because you can build the query in the UI before sending it to the SQL server. In general trying to build a "good for any situation" condition is not easy and not very practical either.



Another thing is that the example only replaced the values of simple conditions with a case statement delivering 1 or 0 depending of the situation. In some special "weird" :o) situation this aproach might be useful, though, I agree.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
Sotiris Filippidis
Sotiris Filippidis
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 9
I agree, each approach can serve a different purpose and different needs. You can build the query dynamically at the BL, you can use dynamic SQL, *or* you can use this approach. It all depends on what you have to do and how you want to do it.



As for the 0 and 1 values, they exist to make specific parts of the CASE statement evaluate. It's not that it's actually returning 0 or 1! Smile

Sotiris L. Filippidis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search