SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Implementing a Dynamic WHERE Clause
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/14/2001 :  23:25:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Garth Wells passed on another article to us. This one covers building a dynamic where clause. And the really neat part is that you don't actually need to write dynamic SQL. This is soooo much simpler.

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 01/15/2001 :  09:47:13  Show Profile  Reply with Quote
Great Article -- Keep them coming

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/15/2001 :  18:19:07  Show Profile  Reply with Quote
So simple and yet so powerful

Great technique. I will be using this quite a bit.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/16/2001 :  17:20:59  Show Profile  Reply with Quote
Very cool

This is a very useful article, thanks!

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/17/2001 :  11:08:27  Show Profile  Reply with Quote
Sal Terillo

Excellent tip! I can definitely use this one.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/19/2001 :  14:27:36  Show Profile  Reply with Quote
Dynamic WHERE Clause

Well, I have eight criteria and sometimes only 6 would be needed, and then COALESCE gets hard to implement.

Perhaps do an article on branching?

I use EXEC(strSQL) to pass dynamically defined queries to SQL Server, but, unfortunately we have Sybase at work, where this won't work.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/22/2001 :  11:40:46  Show Profile  Reply with Quote
Dynamic WHERE Clause

I didn't think COALESCE function could do that, so that's good information. I used the ISNULL function which does the same thing.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/23/2001 :  11:28:50  Show Profile  Reply with Quote
A+

Good article. This is a very slick way of doing dynamic WHEREs.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/23/2001 :  15:16:03  Show Profile  Reply with Quote
Dynamic WHERE clause

I have done some testing using COALESCE function.Query analyzer showes that index can not be used on column we apply this function against.This really hurts performance.There is a huge performance difference when you run query with COALESCE versus even dynamic SQL . Number of logical scans is much higher for query with COALESCE .

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/24/2001 :  13:09:37  Show Profile  Reply with Quote
Like 'P%' only looks at first two charaters.

Can anyone comment on the fact that when used with coalesce, Like 'x%' only searches for two letter words that begin with x?

Please email me at pkarhatsu@yahoo.com

My developers are pretty exceited over this ass we use dynamic where extensively. We also use the Like option most of the time.

I can always have them use like 'x%%%%%%%' to cover the whole field.

Thanks in advance, great article

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/24/2001 :  13:22:00  Show Profile  Reply with Quote
Like 'P%' only looks at first two charaters.

use COALESE with caution cause it may cause table scans.
ISNULL actually works faster.

You may want to look into using LIKE 'H_' or LIKE 'H__', etc if you know the size of the field you are searching on.

Believe it or not, it can sometimes be faster to use full text searching.

there are other methods beyond those mentioned here.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/24/2001 :  21:35:20  Show Profile  Reply with Quote
Functions, Comparision Operations and Indexes

A non-clustered index cannot be used when a function (e.g., SUBSTRING(), UPPER()) is used in a comparison operation in the WHERE clause. I should have mentioned this in the article.

If the datasets you are working with are large, test this solution before putting it into production.

Garth

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/29/2001 :  18:58:15  Show Profile  Reply with Quote
Dynamic SQL WHERE Clauses

I do something similar but handles LIKE expressions also...

getDataSQL = "SELECT * FROM someTable WHERE 1 = 1 "

now If I need a city, state, or zipcode, then do this....

If Len(Trim(Request("City"))) > 0 Then city = Trim(Request("City")
getDataSQL = getDataSQL & "AND city = '%" & city & "'"

If Len(Trim(Request("State"))) > 0 Then state = Trim(Request("State")
getDataSQL = getDataSQL & "AND state = '%" & state & "'"

If Len(Trim(Request("ZipCode"))) > 0 Then zc = Trim(Request("ZipCode")
getDataSQL = getDataSQL & "AND zipCode = " & zc

.....

execute getDataSQL

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/02/2001 :  14:32:13  Show Profile  Reply with Quote
Dynamic SQL WHERE Clauses

THis is a very helpful article, thanks, I've been dyding over 8+ parameters in a sql query if's everywhere, loops galore, double ands etc.... thanks from an ASP guy.....

Go to Top of Page

youeee
Starting Member

United Kingdom
5 Posts

Posted - 05/31/2001 :  07:32:43  Show Profile  Visit youeee's Homepage  Send youeee a Yahoo! Message  Reply with Quote
OK I think that this articale is great but how do I do like '%xxx%'
or maybe ranges ?


Thanks Youeee

http://www.youeee.com
Go to Top of Page

chrisdrop
Starting Member

1 Posts

Posted - 06/29/2001 :  13:06:40  Show Profile  Reply with Quote
i am an asp/com developer as well; get rid of those loops in your code and move it all to SQL like in these examples, HOWEVER; I used this COALESCE() solution for a while and i found a better one, one that GREATLY IMPROVES PERFORMANCE OVER COALESCE() (which is a total dog as performance goes)

in stead, use:
(@param IS NULL OR colname = @param)

it is great..

COALESCE() is also bad at OR's in my experience ie;

WHERE
(@id IS NULL OR rec.order_id = @id)
AND
(@email IS NULL OR rec.bill_to_email = @email)
AND
(@lname IS NULL OR rec.bill_to_lastname = @lname)
AND
(@zip IS NULL OR
(
(sai.Zip = @zip)
OR
(rec.bill_to_zip = @zip)
OR
(items.ship_to_zip = @zip)
)
)
that would be difficult to add with COALESCE()

Good Luck,
Chris

Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 06/30/2001 :  17:06:13  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
There are probably lots of faster ways to do this but you could try this:

set @param = '%' ** @param ** '%'

(note: put a plus sign instead of **. Dunno why but I can't get a plus sign to show in this window??)

select ....

where ...
and ((@param = '%%') or (param = @param))

- Andrew.
 
 
 


Go to Top of Page

JohnNowak
Starting Member

New Zealand
2 Posts

Posted - 06/30/2001 :  17:24:29  Show Profile  Reply with Quote
What would happen if a user entered "alabama; delete * from city;" as thier search criterea? (assuming the web from had a text box rather than drop-down).

I'm guessing the first ; would complete the search statement and the SQL would go on to execute the delete statement. Is there a risk of this?

So far I have found that if you run the following - badly coded - proc:

CREATE PROCEDURE overflowTest
@whereText VARCHAR(4000)
AS
DECLARE
@SQL VARCHAR(4000)
BEGIN
-- where n1 is a numeric field
SET @SQL = 'SELECT * FROM table WHERE n1 = ' + @whereText
EXEC(@SQL)
END
GO

you run the proc as following:
overflowTest '1; delete * from table'

...causing the statement(s) after the ; to execute. This is easy to fix (@whereText should be an int) but it's worth watching out for.

Edited by - JohnNowak on 07/01/2001 18:06:35
Go to Top of Page

Barlow
Starting Member

1 Posts

Posted - 08/14/2002 :  13:56:36  Show Profile  Reply with Quote


I’ve been having a problem with using COLESCE. Everything seems to work fine, expect when a field has a NULL value. If the record has a NULL value the record is not returned.

Example of Data:
OrderID,FirstName,MiddleName, LastNane
=========================================
1,James,Michael,Barlow
2,Mark,<NULL>,Jackson
3,Chris,<NULL>,Jackson
4,Jimmy,<NULL>,Johnson
5,Mark,<NULL> ,Grace


Example of using COALESCE in SQL:
DECLARE @OrderID INT
DECLARE @FirstName VARCHAR(50)
DECLARE @MiddleName VARCHAR(50)
DECLARE @LastName VARCHAR(50)


SET @OrderID = NULL
SET @FirstName = NULL
SET @MiddleName = NULL
SET @LastName = NULL


SELECT OrderID, FirstName, MiddleName, LastName
FROM TestCoalesce
WHERE
OrderID = COALESCE(@OrderID, OrderID) AND
FirstName = COALESCE(@FirstName, FirstName) AND
MiddleName = COALESCE(@MiddleName, MiddleName) AND
LastName = COALESCE(@LastName, LastName)



The only record that is returned is where OrderID = 1. None of the other records are return. It seems while using COALESCE in a where statement and a field being NULL the record will not be returned.

How can I work around this problem, so all records are returned, even the records with NULL?


Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/14/2002 :  14:10:46  Show Profile  Reply with Quote
Example of using COALESCE in SQL:

DECLARE @OrderID INT
DECLARE @FirstName VARCHAR(50)
DECLARE @MiddleName VARCHAR(50)
DECLARE @LastName VARCHAR(50)


SET @OrderID = NULL
SET @FirstName = NULL
SET @MiddleName = NULL
SET @LastName = NULL


SELECT OrderID, FirstName, MiddleName, LastName
FROM TestCoalesce
WHERE
OrderID = COALESCE(@OrderID, OrderID) AND
FirstName = COALESCE(@FirstName, FirstName) AND
coalesce(MiddleName,'Page47') = COALESCE(@MiddleName, MiddleName,'Page47') AND
LastName = COALESCE(@LastName, LastName)


 


Jay White
{0}
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 10/03/2002 :  23:05:13  Show Profile  Reply with Quote
I was working on this problem when I came accross this thread. Good timing and thanks..

The question of the overhead of coalesce that Chris raised didn't seem to gather much support in the follow-up posts. My own preference was a notation used in one of Rob Volk's posts which is similar to Chris' solution

WHERE Column = IsNull(@Column, Column)

Not sure if there is any advantage or disadvantage. I suspect both WHERE statements compile to the same result. I'm surprised that Coalesce is any different in performance from any of the above solutions. Chris probably has evidence otherwise.

Looking forward to comments on this.
-------------------------------------

I ran into the same problem Barlow pointed out on how these dynamic where eliminate rows when a column has a null value.

Jay's solution to the NULL column problem handles equality comparisons, but if the column could be constrained to NOT NULL, it would avoid the problem he solves altogether. I took the NOT NULL constraint approach wherever possible. One datetime column I have (that contains NULLs) must support queries of equality or range (using BETWEEN). This presented a special problem for BETWEEN comparisons.

If NULLs where not a problem, the dynamic where for a datetime column might look like

WHERE
RegDate BETWEEN
IsNull(@RD1, RegDate) AND -- Low Date Value
IsNull(@RD2, IsNull(@RD1, RegDate)) -- High Date Value

But any row with a NULL Regdate is excluded from the selection WHEN @RD1 or @RD2 are NULL (not passed as a selection criteria). It seemed to me that Chris had the right idea, it makes for unusually long SQL but..

WHERE
(
RegDate BETWEEN
IsNull(@RD1, RegDate) AND -- Low Date Value
IsNull(@RD2, IsNull(@RD1, RegDate)) -- High Date Value

OR IsNull(@FL1,IsNull(@FL2,RegDate)) IS NULL) -- RegDate IS NULL
)

Is there's a better way to do this?
-------------------------------------

This thread is stretching queries into complex shapes for the sake of avoiding dynamic SQL. (or is there some other reason?). Any comments on how the performance of dynamic where compares to building an SQL string and crunching it with an EXEC? Using EXEC 'string' would avoid all the IsNull, COALESCE, NULL column value issues and possibly result in a shorter WHERE statement (maybe faster?) ? Does anyone have some measured timing figures that could be posted?

Sam

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000