Conditional Statements in WHERE Clauses

Question

Conditional Statements in WHERE Clauses

Viewing 6 posts - 31 through 35 (of 35 total)

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

Paul Paiva SSCrazy Points: 2237 More actions · Answer 1

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 SSC Enthusiast Points: 140 More actions · Answer 2

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 🙂 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 SSCrazy Points: 2398 More actions · Answer 3

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 SSC Enthusiast Points: 140 More actions · Answer 4

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. 🙂

Sotiris L. Filippidis

the sqlist SSCrazy Points: 2398 More actions · Answer 5

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. 🙂

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 SSC Enthusiast Points: 140 More actions · Answer 6

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! 🙂

Sotiris L. Filippidis