Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional Statements in WHERE Clauses


Conditional Statements in WHERE Clauses

Author
Message
Ray Herring
Ray Herring
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 566
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
DBA_Rob
DBA_Rob
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 336
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
mfisher-496787
mfisher-496787
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
RyanRandall (9/28/2007)
I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.html



However, for the example presented by the article, can someone tell me what's wrong with simply this?...



select 

customerid, companyname, country

from

customers

where

(companyname LIKE @companyname) AND (country LIKE @country)



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





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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36062 Visits: 18736
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
peggy pacheco
peggy pacheco
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 16
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.
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
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,


* Noel
steve block
steve block
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1563
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



Jim K-395923
Jim K-395923
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 31
Great Idea!
Fozzie
Fozzie
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1172
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.



CREATE PROCEDURE usp_getMyData

@orderBy varchar(20)

AS



SELECT personId, birthDate, firstname, surname, email

FROM dbo.persons

ORDER 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

DESC



GO



Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7639 Visits: 18062
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.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
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