| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
youeee
Starting Member
United Kingdom
5 Posts |
 Posted - 05/31/2001 : 07:32:43
|
OK I think that this articale is great but how do I do like '%xxx%'
or maybe ranges ?
Thanks Youeee
http://www.youeee.com |
 |
|
|
chrisdrop
Starting Member
1 Posts |
Posted - 06/29/2001 : 13:06:40
|
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
|
|
|
|
aclarke
Posting Yak Master
Canada
133 Posts |
Posted - 06/30/2001 : 17:06:13
|
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.
|
|
|
|
JohnNowak
Starting Member
New Zealand
2 Posts |
Posted - 06/30/2001 : 17:24:29
|
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 |
|
|
|
Barlow
Starting Member
1 Posts |
Posted - 08/14/2002 : 13:56:36
|
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?
|
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/14/2002 : 14:10:46
|
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} |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 10/03/2002 : 23:05:13
|
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
|
|
|
|
Topic |
|
Article: Implementing a Dynamic WHERE Clause
