Article: Implementing a Dynamic WHERE Clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Implementing a Dynamic WHERE Clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-01-14 : 23:25:16

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.

Barlow
Starting Member

1 Post

Posted - 2002-08-14 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-14 : 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

3467 Posts

Posted - 2002-10-03 : 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

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 08:09:15

Sam,

Take a look at the execution plans of


declare @lname varchar(50)

select *
from dbo.authors
where au_lname = coalesce(@lname,au_lname)

select *
from dbo.authors
where @lname is null or
      au_lname = @lname

You should see that they both use the same index scan and filter, and the coalesce has a 0.0001 improvement on subtree cost. A very minimal difference. I usually use the coalesce because it is easier to type and easier to comment out for debugging. No other reason.

As to the question on Dynamic SQL vs. the Coalesce...
The best way to know which is better is to simply write it both ways and give it a try. There are far to many factors involved to make the decision without trying (IMHO). My experience has been that a dynamic SQL solution only performs better when there are many conditions in the where clause. If there are just one or two conditions in the where clause I will likely use the coalesce solution. If there are 4 or 10 I would likely try both. If there are 50, I would go with dynamic sql.

Jay White
{0}

skjoldet
Starting Member

1 Post

Posted - 2002-12-24 : 04:51:38

Great article. What if I want to control the expression operators, for example:

Cus_City = COALESCE(@Cus_City,Cus_City)
Cus_City <> COALESCE(@Cus_City,Cus_City)

How can send even = or <> to the SP and implement it in my SP?

/Lars

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2002-12-24 : 05:12:33

I have a query for the artical writer.

I beleive although correct me if I'm wrong, being newish to this sql lark, that if you do something like

select blah
from table
where col1 = col1

you wont return the rows where col1 has values of null.

If you take this in to your COALESCE where clause and the user wants to search on col2, but col1 has some null values wont you end up dropping these rows from your result set?

Does this make sense

Rob

verronep
Starting Member

15 Posts

Posted - 2002-12-24 : 09:15:35

Very interesting article, but the way I usually do these types of queries is like so:

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM @Customers
WHERE (Cus_Name = @Cus_Name OR @Cus_Name IS NULL) AND
(Cus_City = @Cus_City OR @Cus_City IS NULL) AND
(Cus_Country = @Cus_Country OR @Cus_Country IS NULL)

The thing to be careful with when using the COALESCE function as in the article is when the table to be searched on contains NULL values. THe COALESCE function will not return such rows.

For example, try allowing NULL values into the Customers table in the article, and insert the following, then rerun the query.
INSERT @Customers VALUES ('Acme', 'Paris', NULL)

You'll see that the new row is not returned, though it should be.
The problem lies in the equality of NULLS with SQL Server.
IF NULL=NULL SELECT 1 ELSE SELECT 0 returns 0. SQL Server doesn't equate NULL with anything, not even NULL. The above method solves this problem.

I did some testing on a fairly large table to see if I could find any difference in speed or performance. (The table had 4.5 million rows, no NULL value are allowed in the table.)

Interestingly enough, @Param IS NULL approach and the COALESCE approach used the same query plan, but different indexes. (Not sure why, to be honest.)

@Param IS NULL took 38% of the time, while COALESCE took up 62% when I ran the selects as a batch.
Forcing both queries to use @Param IS NULL approach's index only made the disparity greater. 12% for the @Param IS NULL approach, with 88% for COALESCE.
Forcing both queries to use the COALESCE approach's index even things up a bit, with the @Param IS NULL approach taking 49% to COALESCEs 51%. Not much difference there...

Interesting stuff.

Paul

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

verronep
Starting Member

15 Posts

Posted - 2002-12-24 : 09:49:26

SQL Server just loves to confuse me...

In my previous post, I tested the following query:
SELECT Cus_Name,
Cus_City,
Cus_Country
FROM @Customers
WHERE (Cus_Name = @Cus_Name OR @Cus_Name IS NULL) AND
(Cus_City = @Cus_City OR @Cus_City IS NULL) AND
(Cus_Country = @Cus_Country OR @Cus_Country IS NULL)

I then realized it seemed to make more sense for the @Param IS NULL to be first. (With the premise that if the first condition in an OR statement is true, it wouldn't process the second half of the statement.)
So I ran this query side by side to see, and was a bit surprised by the results.
SELECT Cus_Name,
Cus_City,
Cus_Country
FROM @Customers
WHERE (@Cus_Name IS NULL OR Cus_Name = @Cus_Name) AND
(@Cus_City IS NULL OR Cus_City = @Cus_City) AND
(@CusCountry IS NULL OR Cus_Country = @Cus_Country)

The first query actually runs 1.6 times faster, and the actual execution plans are completely different, as are the indexes used.
For the second time ever I saw a query with parallelism in the execution plan. (The first one uses parallelism.)

I don't understand at all why the vast difference in execution plans and indexes at this point. If anyone could enlighten me, it'd be much appreciated.

(Just a note, I used teh same 4.5 million row table as in the first query, and it's not a customer table, just used the customer table to illustrate the syntax of my queries.)

Paul

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Edited by - verronep on 12/24/2002 09:52:23

gully
Starting Member

2 Posts

Posted - 2003-10-28 : 10:54:50

I have tryied COALESCE and the IS NULLL approach on 200 000 records
EXEC (SQL) is still faster ........

Sir code a lot

jkincaid
Starting Member

1 Post

Posted - 2003-11-22 : 23:49:46

Is it possible to pass the operator (+,-,>=, LIKE) to use in a COALESCE SP??

quote:
Originally posted by skjoldet

Great article. What if I want to control the expression operators, for example:

Cus_City = COALESCE(@Cus_City,Cus_City)
Cus_City <> COALESCE(@Cus_City,Cus_City)

How can send even = or <> to the SP and implement it in my SP?

/Lars

zelk
Starting Member

6 Posts

Posted - 2004-02-04 : 12:28:09

I check the value against NULL and achieve good performance and flexibility. No problem with LIKE values either:

WHERE
(@title IS NULL OR title LIKE @title)
AND
(@min_release_date IS NULL OR release_date >= @min_release_date)
AND
(@document_id IS NULL or document_id = @document_id)

Great performance and flexibility! All indexes are used as expected.

joseph_hurley
Starting Member

4 Posts

Posted - 2004-06-04 : 14:01:38

To allow NULL values using coalesce you could use.
WHERE ISNULL(Table.Column,0) = COALESCE (@Variable, ISNULL(Table.Column,0))

Much easier to read than breaking out in a Case statement. I haven't checked the performance difference, but it does work. This is neat too:

WHERE (Table.Column IN(SELECT Column FROM dbo.ParseFunction(@Variable,','))
OR (ISNULL(Table.Column,0) =
CASE
WHEN @Variable IS NULL then ISNULL(Table.Column,0)
END
))

dbo.ParseFunction is a function that splits a string using the delineator passed into multiple rows.

This allows you to check against multiple values input in CSV format.

gnguyen
Starting Member

1 Post

Posted - 2005-08-15 : 21:00:21

Thanks guys,

This article is excellent. It has really helped me solving a problem that has been "bugging" me for the last 3-4 days.

X amount of brains is by far better than 1/2 a brain

love
Starting Member

2 Posts

Posted - 2005-11-03 : 10:03:56

Its good article and good info when there is only one table involved.

How about when there is Join Present. How whould you build Dynamic SQL.

For Eg.

Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests

Table3 holds Customers Interests.

Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3

Now If I want to run a query:

Now lets say on my webpage I represent Interests as CheckBoxes. I select Interests I1, I3, I4

So query will be:

Give me list of Customers with Interests I1, I3, I4

How would you run such Dynamic Query?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-28 : 09:25:52

I agree with the comments here about using coalesce for searches. It caused an index scan instead of seek when passing in null for any of the params. So I've gone back to checking (inside the sp) what param values have been passed in and then calling the relative SELECT statement. That way I get my SEEKS back (and no dynamic sql required) and I'm a happy man :-)

caractacus
Starting Member

2 Posts

Posted - 2006-05-24 : 12:12:22

An alternative that solved my problem, and produced good query plans is as follows:

CREATE PROCEDURE SampleProc
@a uniqueidentifier,
@b uniqueidentifier,
@c uniqueidentifier
AS

-- convert NULL parameters to 'empty' values
-- NULLs cannot be correctly compared using this pattern
SET @a = ISNULL(@a, 0x00)
SET @b = ISNULL(@b, 0x00)
SET @c = ISNULL(@c, 0x00)

SELECT a.col, b.col, c.col
FROM a inner join b inner join c
WHERE
CASE WHEN @a = 0x00
THEN @a
ELSE a
END = @a
AND
CASE WHEN @b = 0x00
THEN @b
ELSE b
END = @b
AND
CASE WHEN @c = 0x00
THEN c
ELSE @c
END = @c

What's the thinking, why do this?

The idea is that the CASE statements are dynamically evaluated by the query optimizer. When the GUIDs are 0x00 then the filtered column does not participate in the query _AT ALL_.

The problem with using COALESCE, and ISNULL, is that the filtered column name always participates in the query, which has impact on the query optimizer. This particular technique excludes the non-participating columns and allows for selection of an optimal query plan.

When all the GUIDs are zero, the WHERE clause evaluates to

WHERE 0x00 = 0x00 AND 0x00 = 0x00 and 0x00 = 0x00

These terms are optimized out of the query.

James Caradoc-Davies
caractacus

Dhananjay3
Starting Member

1 Post

Posted - 2006-06-22 : 07:13:50

Neat trick with coalesce that! And the comments were just as informative as the article. Thanks Guys!

net_prog
Starting Member

1 Post

Posted - 2006-12-26 : 19:05:54

I agree that static query is more efficient than the dynamic one, but I suppose it is better to use dynamic one when each search parameter adds an INNER JOINed table to the query. So, if you have 5 parameters, in static query you will have to join all 5 tables regardless of parameter values. And that could mean resource wasting if those 5 tables have tons of records to be joined. With dynamic query you can specify not to join any of them if there are no search values and add INNER JOINs one by one according to the values.

sandesh
Starting Member

1 Post

Posted - 2009-02-25 : 02:51:50

It is of great use

Thank you very much

Sandesh
http://sanshark.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources