Doing Short Circuiting in WHERE clause
-
For simplicity sake, say I have a table that contains 2 fields....FirstName and LastName.
I would like to write the query so that it satisfies the following....
If first name is supplied, a SELECT is performed based on first name only. If last name is supplied then by last name only. But if both are supplied, then both are used in the query.
I understand one can use CASE to do "short-circuiting". If so, how would one code the above?
Thanks.
-
Not exactly using CASE, but this should do the trick :
SELECT *
FROM MyTable
WHERE IsNull(FirstName, <Name to look for>) = <Name to look for>
AND IsNull(LastName, <Name to look for>) = <Name to look for> -
Just reread your question, if you want to do the opposite, meaning that you pass in parameters for FirstName and LastName, you can use the same construct :
SELECT *
FROM MyTable
WHERE FirstName = IsNull(@FirstName, FirstName)
AND LastName = IsNull(@LastName, LastName) -
If @FirstName and @LastName are the variables and contain NULL for 'Not Supplied' then
WHERE (@FirstName IS NULL OR @FirstName = FirstName)
AND (@LastName IS NULL OR @LastName = LastName)
or if you want to test in sequence then
WHERE (CASE
WHEN @FirstName IS NOT NULL AND @LastName IS NOT NULL AND @FirstName = FirstName AND @LastName = LastName THEN 1
WHEN @FirstName IS NOT NULL AND @FirstName = FirstName THEN 1
WHEN @LastName IS NOT NULL AND @LastName = LastName THEN 1
ELSE 0
END) = 1
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks guys....exactly what I was looking for. Man, this is a great place to learn from.
-
you've got the solutions. Remember that "short circuits" overheat ! If you use to much short circuits, your box might fireup. Do some performance checks as to what costs your shortcuts bring. (index usage, filtering,...)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
alzdba. I'm "only" doing it for search forms. In my search forms, I let the user enter whatever details they want....like say BusinessName, ContactType LocationType, Location, etc.
Just curious, how would you tackle this? Maybe I'm doing it wrong. The only other way I can think of, is to do it with dynamic SQL where I can customize the WHERE clause accordingly.
I wanted to stay away from dynamic SQL because I was under the impression that performance would be worse. Or is this incorrect? Dynamic SQL would sure make things easier under this circumstances.
--Lenard
-
One possibility is using stored procedure groups. I tend to like this choice because it offers a clean approach, with the advantage of stored procedures over dynamic sql, and the advantage of having each variation compile its own execution plan.
A stored procedure group example:
CREATE PROC GetStuff
@FirstName VARCHAR(30) = NULL
, @LastName VARCHAR(30) = NULL
AS
BEGIN
IF @FirstName IS NULL BEGIN
IF @LastName IS NULL BEGIN
EXEC GetStuff;2
END
ELSE BEGIN
EXEC GetStuff;3 @LastName
END
END
ELSE BEGIN
IF @LastName IS NULL BEGIN
EXEC GetStuff;4 @FirstName
END
ELSE BEGIN
EXEC GetStuff;5 @FirstName, @LastName
END
END
GO
-- All values
CREATE PROC GetStuff;2
AS
SELECT Columns FROM StuffTable
GO
-- Values filtered by LastName
CREATE PROC GetStuff;3
@LastName VARCHAR(30)
AS
SELECT Columns
FROM StuffTable
WHERE LastName = @LastName
GO
-- Values filtered by FirstName
CREATE PROC GetStuff;4
@FirstName VARCHAR(30)
AS
SELECT Columns
FROM StuffTable
WHERE FirstName = @FirstName
GO
-- Values filtered by both first and last name
CREATE PROC GetStuff;5
@FirstName VARCHAR(30)
, @LastName VARCHAR(30)
AS
SELECT Columns
FROM StuffTable
WHERE FirstName = @FirstName
AND LastName = @LastName
GOYou can execute either explicitly or implicitly:
--Implicitly execute GetStuff;3
EXEC GetStuff @LastName='Smith'
--Explicitly execute GetStuff;3
EXEC GetStuff;3 'Smith' -
Don't do it in a stored procedure!
To begin with, using CASE or ISNULL functions can prevent index optimizations and force index or table scans in any query. But if the presence or absence of a particular join or filter condition changes the execution plan you could be asking for trouble. I've had more developers come whining to me about web page timeouts caused by this technique than all their other bad SQL habits combined.
It works fine when they test it in Query Analyzer, but in procs I've seen the execution time go from a few seconds to 30 minutes (an extreme case).
When the proc is compiled it can only save one execution plan, and I believe SQL Server assumes all the optional params are non-null. When you call the proc with null parameters it uses this execution plan whether it makes sense or not.
Dynamic SQL works fine in this case (especially with sp_executesql), where the penalty for recompiling the query is more than offset by using the best execution plan for the given parameters. The other method is to code all the alternate forms of the query and use IF statements to choose between them, but this gets out of hand very quickly.
-
Scott,
Agree with all of your points. I prefer the stored procedure groups because it mitigates the negative affects of the stored procedure recompilations and the optimizer's index confusion, but allows the procedure to fully encapsulate the intent of the query.
Advantages to stored procedure groups:
-- No messy dynamic SQL within the client or middle tier
-- Compiled execution plans for each variation
-- Flexibility to implicitly or explicitly call procedure
-- Encapsulation of procedure within DB (not middle tier or client side)
-- Can delete all at once (DROP PROC GetStuff will drop all in group).
-
JPipes and Scott Coleman excellent discussion
. Similar problems with a web app but with lots of summations and filters. -
I like jpipes' solution. Even though it beat my repsonse by half an hour, I hadn't seen it before I started writing.
The only problem is the number of variations goes up exponentially with the number of optional parameters.
-
Actually, I did think of implementing 'jpipes' method before I posted. But when you give users 4 to 8 different ways to tailer their search, like Scott says, the permutations sure add up quickly.
I think that some of my search senerios will likely bias one over another method.....albeit not necessarily from a performance aspect. I've found in the past when you granulize code "too" much, it gets harder to maintain and follow code.
I think for the really complicated search senarious I will use Dynamic SQL but otherwise implement jpipes suggestion.
I have a followup question. I have a couple of functions that I've created. The functions return a table (ie. results from a search senario). I'm now using them my JOINS. Is it "better" to use them there or in a WHERE clause? Earlier, I was using them with an WHERE EXISTS. (I don't much data to do a test on which is why I'm posing the question.)
--Lenard
-
Can you post a small example
I try limit apply the filter as close to the source of the data as possible. Example in the ON clause rather than the where if possible.
-
Here it is. This is one of the examples where I was planning on reworking the WHERE clause as per this thread. The function that I was referring to is called dbo.LocationsFoundInCountry. I'm planning on using Dynamic SQL on this because the user may not have entered any location criteria, or a combination of search criterias in the WHERE clause. In a previous version I had the LocationsFoundInCountry in my WHERE clause using an EXISTS statement.
SELECT
Business.ACID, Business.Name, dbo.FullName(Person.FirstName, Person.LastName, Person.EmployeeNum)
+ ' / ' + dbo.DateFormat(Business.SubmittedOnGMT) AS SubmittedByOn, LocationsFound.ID
FROM
dbo.LocationsFoundInCountry( @country, @location ) LocationsFound INNER JOIN
Business INNER JOIN
Person ON Business.SubmittedByACID = Person.ACID INNER JOIN
Business BusinessX INNER JOIN
BusinessDetail ON BusinessX.ACID = BusinessDetail.AirportDesignatorACID ON Business.ACID = BusinessDetail.ACID INNER JOIN
ContactType ON Business.ContactTypeID = ContactType.ID ON LocationsFound.ID = BusinessDetail.CountryAssignID
WHERE
Business.IsActive = @actives AND
Business.ContactTypeID = ISNULL( @contactTypeID, Business.ContactTypeID ) AND
BusinessDetail.BusinessLocationTypeID = ISNULL( @locationTypeID, BusinessDetail.BusinessLocationTypeID ) AND
BusinessX.Identifier = ISNULL( @areaDesignator, BusinessX.Identifier )Edited by - lenardd on 07/29/2003 11:09:36 PM
. Similar problems with a web app but with lots of summations and filters.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply