SQLServerCentral / Discuss Content Posted by Sotiris Filippidis / Article Discussions / Article Discussions by Author / Conditional Statements in WHERE Clauses / Latest Posts

RE: Conditional Statements in WHERE Clauses

Sotiris Filippidis — Wed, 10 Oct 2007 06:29:24 GMT

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! :)

RE: Conditional Statements in WHERE Clauses

the sqlist — Wed, 10 Oct 2007 06:21:35 GMT

[quote][b]Sotiris Filippidis (10/10/2007)[/b][hr]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. :)[/quote]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.

RE: Conditional Statements in WHERE Clauses

Sotiris Filippidis — Wed, 10 Oct 2007 06:08:03 GMT

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. :)

RE: Conditional Statements in WHERE Clauses

the sqlist — Wed, 10 Oct 2007 05:56:32 GMT

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, countryfrom customerswhere --companyname LIKE '%the%'--AND companyname NOT LIKE 'A%'AND country='Germany'Plain and simple.

RE: Conditional Statements in WHERE Clauses

Sotiris Filippidis — Thu, 04 Oct 2007 14:11:03 GMT

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.

RE: Conditional Statements in WHERE Clauses

Paul Paiva — Thu, 04 Oct 2007 10:15:19 GMT

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

RE: Conditional Statements in WHERE Clauses

Matt Miller (#4) — Thu, 04 Oct 2007 08:44:28 GMT

I've used the conditional syntax in WHERE clauses, but a conditional ORDER BY? That essentially guarantees not using indexes, and ORDER BY without being able to use indexes tends to be VERY costly. A conditional where clause is a linear growth (N growth), whereas a conditional ORDER by would entail something like hash sorts/bubble sorts (N^2 growth), so the effort grows polynomially.Are you actually ever seeing it use anything index related using that technique? I'd be really curious if you did - I just don't see how it would.

RE: Conditional Statements in WHERE Clauses

Fozzie — Wed, 03 Oct 2007 09:36:31 GMT

Doing this type of thing in the order by clause.. now that's the way forward!If you have 20 lines of sql in an sp and have to order the results in 5 different ways, say by date, firstname, surname, personId, email... that's when you can really cut down on your code in your stored procedure, and avoid 'dynamic queries'.I don't give any permissions on tables to client applications.. maybe I am harsh, but I like the added layer.[code]CREATE PROCEDURE usp_getMyData @orderBy varchar(20)ASSELECT personId, birthDate, firstname, surname, emailFROM dbo.personsORDER BY CASE @orderBy WHEN 'personId' THEN personId END DESC, CASE @orderBY WHEN 'firstname' THEN firstname WHEN 'surname' THEN surname WHEN 'email'THEN email END DESC, CASE WHEN @orderBy IS NULL THEN birthDate END DESCGO[/code]

RE: Conditional Statements in WHERE Clauses

Jim K-395923 — Tue, 02 Oct 2007 02:11:43 GMT

Great Idea!

RE: Conditional Statements in WHERE Clauses

steve block — Mon, 01 Oct 2007 08:06:19 GMT

I too had a problem reading the posted script in the original article because I could not see the Vertical Scroll Bar and the right edge of the Horizontal Scroll Bar. I tried maximizing the window and this still didn't help.Steve

RE: Conditional Statements in WHERE Clauses

noeld — Fri, 28 Sep 2007 11:32:20 GMT

Dynamic query construction should be left to the Client Side. As long as you do that you won't loose flexibility and with the use os sp_execusql some of those could even be reused.Cheers,

RE: Conditional Statements in WHERE Clauses

peggy pacheco — Fri, 28 Sep 2007 10:19:16 GMT

The conditional where clause works fine for small sets of data but it is not very efficient. For large data sets it is better to stick to the dynamic sql approach.

RE: Conditional Statements in WHERE Clauses

Steve Jones - SSC Editor — Fri, 28 Sep 2007 09:33:46 GMT

I'm not sure you can avoid it for that query, but you can inject intelligence into your application. If you have exact searches, then use a query (or proc) that works for those. If you need less exact searches, then switch to a new proc and take the hit. Code is fairly cheap and you don't necessarily have to reuse stuff if there's a performance hit.You have to compare the cost and time of developing (and maintaining) more procs v the hit your users and systems take. If you have any sort of search volume, it's worth doing a little work to optimize different searches.

RE: Conditional Statements in WHERE Clauses

mfisher-496787 — Fri, 28 Sep 2007 09:03:41 GMT

[quote][b]RyanRandall (9/28/2007)[/b][hr]I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.htmlHowever, for the example presented by the article, can someone tell me what's wrong with simply this?...[code]select customerid, companyname, countryfrom customerswhere (companyname LIKE @companyname) AND (country LIKE @country)[/code]This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).[/quote]That was my first reaction, too. On re-reading the article I see that the point was not simply to write SQL which fits the given example. It was meant to be a generalizable solution for user-defined searches.My habitual solution when building search forms has simply been to write code that conditionally concatenates SQL fragments which reflect user-selected criteria and operators. So the whole discussion here is interesting. Thanks.

RE: Conditional Statements in WHERE Clauses

DBA_Rob — Fri, 28 Sep 2007 08:32:59 GMT

To me its all about knowing your data. This is a great method to use some of the time, but it will cost you in performance in some cases. At our shop we have a very large database (which happens to be on Oracle), but when we are trying to perform an extract to a data mart (SQL Server) we generally do not want to use an index anyway because the volume is so large that the indexes are many times less efficient than a table scan. In this environment the technique described in this article would be good. Thanks

RE: Conditional Statements in WHERE Clauses

Ray Herring — Fri, 28 Sep 2007 06:56:50 GMT

I don't see how any query can avoid a table scan given a condition like "all company names that contain the word THE".I have used this technique to meet some complex requirements for a flexible search proc. I structured the code a little differently by placing the constant first (before the case). From my reading of SARGs I think (don't know) that would help performance some. Also, I always include a default value for the Case.e.g. where 1 = Case ....... else 1 end

RE: Conditional Statements in WHERE Clauses

RyanRandall — Fri, 28 Sep 2007 05:17:01 GMT

[quote][b]Steve Coleman (9/28/2007)[/b][hr]Sorry but I couldn't see the whole example. The scroll bars on the example did not work.Steve[/quote]Steve,It looks like this...-- Declare some local variables. Actually, we are creating a pair of variables-- for each column included in our WHERE clause.-- The first variable represents the value we are filtering and the second-- represents the "operator" for the filter.declare @companyName varchar(255)declare @companyNameOp varchar(2)declare @country varchar(255)declare @countryOp varchar(2)-- Let's set some sample values now. The values you see here represent the second-- of the two scenarios described above, i.e. all records for companies located in Germany,-- excluding companies starting with the letter A-- Operators are defined here with arbitrary, two-letter values. -- Of course you could define your own set of operators, with different-- naming conventions. For our example, here's the meaning of each possible-- value:-- ne = not equal-- eq = equal-- bg = begins with-- ed = ends with-- ct = contains-- For our example, we are using only varchar fields in our WHERE clause.-- It is very easy, though, to define operators for other data types as well.set @companyname = 'A%'set @companynameOp = 'ne'set @country = 'Germany'set @countryOp = 'eq'-- Ok, now let's form our query. select customerid, companyname, countryfrom customerswhere case @companyNameOp when '' then 1 -- Operator not defined, get everything when 'eq' then -- Operator is "equals" case when companyname like @companyName then 1 else 0 end when 'bg' then -- Operator is "begins with" case when companyname like @companyName +'%' then 1 else 0 end when 'ed' then -- Operator is "ends with" case when companyname like '%' + @companyName then 1 else 0 end when 'ct' then -- Operator is "contains" case when companyname like '%' + @companyName +'%' then1 else 0 end when 'ne' then -- Operator is "not equal" case when companyname not like @companyName then 1 else 0 end end =1AND-- Same approach for the second field case @countryOp when '' then 1 when 'eq' then case when country like @country then 1 else 0 end when 'bg' then case when country like @country +'%' then 1 else 0 end when 'ed' then case when country like '%' + @country then 1 else 0 end when 'ct' then case when country like '%' + @country +'%' then 1 else 0 end when 'ne' then case when country not like @country then 1 else 0 end end =1

RE: Conditional Statements in WHERE Clauses

Steve Coleman — Fri, 28 Sep 2007 05:09:11 GMT

Sorry but I couldn't see the whole example. The scroll bars on the example did not work.Steve

RE: Conditional Statements in WHERE Clauses

RyanRandall — Fri, 28 Sep 2007 03:41:10 GMT

[quote][b]Ben (9/28/2007)[/b][hr]Exacxtly, though if you throw an 'or @companyName is null' in there the optimiser will ignore any 'parameters' that are not set. [/quote]Yes, I know about that (it's in Erland's article), and it is the best method. I left it out here to keep the example aligned with the article and keep the point clear.

RE: Conditional Statements in WHERE Clauses

Ben-425648 — Fri, 28 Sep 2007 03:33:10 GMT

[quote][b]RyanRandall (9/28/2007)[/b][hr]However, for the example presented by the article, can someone tell me what's wrong with simply this?...[code]select customerid, companyname, countryfrom customerswhere (companyname LIKE @companyname) AND (country LIKE @country)[/code]This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).[/quote]Exacxtly, though if you throw an 'or @companyName is null' in there the optimiser will ignore any 'parameters' that are not set. eg,[code](companyName like @companyName or @companyName is null)AND(country like @country or @country is null)[/code]

RE: Conditional Statements in WHERE Clauses

RyanRandall — Fri, 28 Sep 2007 03:28:36 GMT

I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.htmlHowever, for the example presented by the article, can someone tell me what's wrong with simply this?...[code]select customerid, companyname, countryfrom customerswhere (companyname LIKE @companyname) AND (country LIKE @country)[/code]This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).

RE: Conditional Statements in WHERE Clauses

Ben-425648 — Fri, 28 Sep 2007 02:22:22 GMT

How about:...?set @companyname = 'A%'set @companynameOp = 'ne'set @country = 'Germany'set @countryOp = 'eq'set@companyName = case @companyNameOp when '' then null when 'eq' then -- Operator is "equals" @companyName when 'bg' then -- Operator is "begins with" @companyName +'%' when 'ed' then -- Operator is "ends with" '%' + @companyName when 'ct' then -- Operator is "contains" '%' + @companyName + '%' set@country = case @countryOp when '' then null when 'eq' then -- Operator is "equals" @country when 'bg' then -- Operator is "begins with" @country +'%' when 'ed' then -- Operator is "ends with" '%' + @country when 'ct' then -- Operator is "contains" '%' + @country + '%' -- Ok, now let's form our query. select customerid, companyname, countryfrom customerswhere (companyName like @companyName or @companyName is null)AND(country like @country or @country is null)

RE: Conditional Statements in WHERE Clauses

Carlo Romagnano — Fri, 28 Sep 2007 00:24:59 GMT

In this case, I prefer dynamic sql and add only conditions I need. Performance are granted more than complex and useless conditions. :)

RE: Conditional Statements in WHERE Clauses

Gary Xu — Tue, 26 Oct 2004 23:27:00 GMT

My first ever post. Here are my 2 cents:

Sotiris' article has some good acdemic interest. But just as laperrej pointed out, we can't use anything like that in production because of performance issues.
AKM's answer is not a bad one, for a large, busy system. I would actually push it futher: use a master stored procedure to call sub stored procedures. Yes, it would be lots of typing. But performance is always the key.
Dynamic construct a query is not a bad idea. When you use EXEC sp_executesql @CMD, the plan is most likely cached and reused. Comparing with executing a wrong plan, the time consumed to generate a plan is neglectable
If I am tasked to write such a query, I would do this:

CREATE PROC usp_GetComstomers@CompY VARCHAR(100)='%',@ContY VARCHAR(100)='%',@CompN VARCHAR(100)='NoNothingNoneWhateverString',@ContN VARCHAR(100)='NoNothingNoneWhateverString'AS/*Example:EXEC usp_GetComstomers '%','Germany','A%'EXEC usp_GetComstomers '%the%'*/SET NOCOUNT ONSELECT *FROM dbo.CustomersWHERE CompanyName LIKE @CompY AND Country LIKE @ContY AND CompanyName NOT LIKE @CompN AND Country NOT LIKE @ContN
This would be fast and not too bad looking. :-)

RE: Conditional Statements in WHERE Clauses

Beedles About — Fri, 20 Aug 2004 06:17:00 GMT

Hi all,

Did RonKyle post a sample of his solution as I have a little task which could use his technique.

If not perhaps he could post a sample so we may see how it works and give some feedback.

Regards Steve

RE: Conditional Statements in WHERE Clauses

thormj — Mon, 26 Jul 2004 11:57:00 GMT

Thank you very much; every day I find I know very little about sql (Mainly a C/C++ dude).

Hey RonKyle, could you post an example of the join that you're talking about so we can talk about how it would use the indices? It looks like that might be the cleanest solution yet.

Thanks!

RE: Conditional Statements in WHERE Clauses

Adam Machanic — Mon, 26 Jul 2004 11:08:00 GMT

thormj,The problem is that unfortunately that query will be unable to seek indexes as the optimizer will evaluate ALL of the possible conditions (therefore, a scan will be necessary).

RE: Conditional Statements in WHERE Clauses

thormj — Mon, 26 Jul 2004 08:06:00 GMT

I like RonKyle's solution -- its one I haven't seen before.

But why not a query like this:

select * from customers where

((@countryOP='bg' AND country like @country+'%') or (@countryOP='eq' AND country = @country) or (@countryOP='ne' AND country <> @country) or (@countryOP='')) AND((@companyOP='') or (@companyOP='bg' AND comany like @company+'%') or ...

9/10ths of the statements would be false, but how much of a problem is that?

RE: Conditional Statements in WHERE Clauses

RonKyle — Mon, 26 Jul 2004 07:12:00 GMT

We create a temp table which is then filled in with the resulting values which are then joined through a stored proc to a fixed statement.

In this case, we would populate a temp table with the company codes that meet the desired criteria. We then join that table to the query. The temp table acts as a filter.

It's much simpler than the way described in the article.

RE: Conditional Statements in WHERE Clauses

Adam Machanic — Mon, 26 Jul 2004 07:09:00 GMT

Please check out MVP Erland Sommarskog's article on this subject:http://www.sommarskog.se/dyn-search.html

RE: Conditional Statements in WHERE Clauses

Henrik Staun Poulsen-105872 — Mon, 26 Jul 2004 03:14:00 GMT

Performance might be an issue, but this solution is great when security is an issue. It is very easy to grant execution rights to a sproc, and then block the Select command.

See http://www.novicksoftware.com/Articles/crud-operations-using-sql-server-stored-procedures-part-1.htm about application roles.

RE: Conditional Statements in WHERE Clauses

Sotiris Filippidis — Mon, 26 Jul 2004 03:04:00 GMT

When I was assigned this task, I first thought of a similar approach. But when you have over 10 fields to filter by this can be rather big. You also have to type a lot if / when something like a field name changes.

I created this example having in mind not only the use of operators but also the extension of this query to accept different logical operators between criteria too. Think of it as a dynamic environment where you can add filters to fields and also add operators like AND, OR between criteria. This would be too much typing if you wanted to cover all possible combinations.

RE: Conditional Statements in WHERE Clauses

AKM — Mon, 26 Jul 2004 02:51:00 GMT

I would be interested to see the relative performance of the article's method versus the following approach, which is equally powerful but (I think) can be optimised much more easily by the query engine:

-- declare condition variables as in the article

if @companynameOp='' and @countryOp=''

  select customerid, companyname, country from customers

else if @companynameOp='' and @countryOp='eq'

  select customerid, companyname, country from customers where country like @country

else if @companynameOp='' and @countryOp='bg'

  select customerid, companyname, country from customers where country like @country+'%'

-- etc etc

else if @companynameOp='ct' and @countryOp='ne'

  select customerid, companyname, country from customers where companyname like '%' + @companyName +'%' and country not like @country

-- etc etc

OK it's much more typing but that shouldn't really be considered an issue. As you can see, we are handling each possible pair of comparison operators separately, and each select statement can use indexes, be optimised, etc.

RE: Conditional Statements in WHERE Clauses

Sotiris Filippidis — Mon, 26 Jul 2004 01:31:00 GMT

You are right. My point, though, was to show how an operator-driven query could be performed, actually neglecting any performance issues. There are some cases, for example, a custom query builder inside an application, where a technique like that could prove handy, especially when dealing with ad hoc reporting inside a database with limited data but needing to have many different views.

I will investigate further and maybe come out with an even better solution. Thank you for your comments!

RE: Conditional Statements in WHERE Clauses

laperrej — Sun, 25 Jul 2004 22:42:00 GMT

Although an interesting technique, from a database performance point of view, this is not a very good idea. SARGs are turned into non-SARGs, so indexes won't be used (for an example, rewrite

SELECT * FROM authors WHERE au_lname LIKE 'm%' AND state = 'CA'

in the pubs database with conditional statements in the WHERE clause and compare the execution plans. You will see that the query above uses an index, the query with conditional statements in the WHERE clause does not.

Conditional Statements in WHERE Clauses

Sotiris Filippidis — Fri, 09 Jul 2004 18:07:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/col