dynamic where clause and null values

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

SQL Server 2005 Forums

Transact-SQL (2005)

dynamic where clause and null values

Author

Topic

droog
Starting Member

12 Posts

Posted - 2007-04-16 : 17:29:10

I found an article about implementing a dynamic where clause and am having a problem returning data when their are fields set to "allow nulls".

Here is the sql statement, all fields are set to not null except cust_country. Thanks!

DECLARE @Cus_Name varchar(30),
@Cus_City varchar(30),
@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 17:44:29

What is the problem you have? Is it s synax error? logical error?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

droog
Starting Member

12 Posts

Posted - 2007-04-16 : 17:55:03

It wont return anything. If all fields are "not null" it works, but if i have any fields "allow nulls" it doesnt.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 18:08:32

Try this:

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = (CASE WHEN @Cus_Name IS NULL THEN Cus_Name ELSE @Cus_Name END )
AND Cus_City = (CASE WHEN @Cus_City IS NULL THEN Cus_City ELSE @Cus_City END )
AND Cus_Country = (CASE WHEN @Cus_Country IS NULL THEN Cus_Country ELSE @Cus_Country END )

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

droog
Starting Member

12 Posts

Posted - 2007-04-16 : 18:19:41

I am sure that works, but it defeats the purpose of using COALESCE. It seems to be more robust, clean and straightforward. I just want to know why it isn't returning records if i have a field that allows nulls.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-16 : 18:52:31

Your requirements do not make logical sense if your column allows NULLS.

please read:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/14/60133.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 18:53:23

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-16 : 19:09:47

quote:
Originally posted by dinakar

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..

I don't believe that's the case in his situation since the column is isolated on one side of the predicate:

WHERE ColumnName = SomeFunction(...)

Perhaps this is what you are referring to:

WHERE SomeFunction(ColumnName, ...) = SomeValue

Tara Kizer
http://weblogs.sqlteam.com/tarad/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-16 : 19:29:13

Again, coding is not the issue. Read the link.

- Jeff
http://weblogs.sqlteam.com/JeffS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 19:43:04

quote:
Originally posted by tkizer

quote:
Originally posted by dinakar

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..

I don't believe that's the case in his situation since the column is isolated on one side of the predicate:

WHERE ColumnName = SomeFunction(...)

Perhaps this is what you are referring to:

WHERE SomeFunction(ColumnName, ...) = SomeValue

Tara Kizer
http://weblogs.sqlteam.com/tarad/

you are right Tara.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-16 : 20:22:17

quote:
Originally posted by tkizer

quote:
Originally posted by dinakar

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..

I don't believe that's the case in his situation since the column is isolated on one side of the predicate:

WHERE ColumnName = SomeFunction(...)

Perhaps this is what you are referring to:

WHERE SomeFunction(ColumnName, ...) = SomeValue

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Actually, that is not correct, the column is not isolated on one side ...
The column is referenced on BOTH sides.

This is actually the situation:

WHERE ColumnName = SomeFunction(... ColumnName ... )

- Jeff
http://weblogs.sqlteam.com/JeffS

droog
Starting Member

12 Posts

Posted - 2007-04-16 : 20:59:29

Well i am officially more confused as ever. I read your article and you say:

"(a) come up with an alternate filtering specification; i.e., change the specification to fit your environment
or
(b) edit your schema to disallow NULLs in that column; i.e., change your environment to fit the specification"

So lets say i have a table that looks like:
articleID (PK, int, not null)
articleTitle (varchar(20), not null)
articleAuthID (FK, int, not null)
articleCreateDate (smalldatetime, not null)
articleScheduleDate (smalldatetime, null)

And i want to allow end users to have the ability to search all fields. Lets say in this case we want to search all articles created in the last week that haven't yet been scheduled.

I can't very easily make articleScheduleDate a 'not null' field. So 'b' doesn't seem to be much of a choice. And 'a', are you suggesting creating a separate query statement just for this?

What would you do?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 08:11:30

You need to come up with a new specification.

Right now, your specification is:

if @ColumnName parameter is null, then don't filter on that column. Otherwise, show only rows where ColumnName = @ColumnName.

Right?

Well, if ColumnName is nullable, then this spec won't work. So you need to come up with a new one. Something like this (for date columns for example):

if @ColumnName = '1/1/1900', then don't filter on that column. Otherwise, show only rows where ColumnName = @ColumnName. If @ColumnName is null, show only rows where ColumnName is null.

Which you could then implement like this:


where ((@ColumnName = '1/1/1900') or 
       (@ColumnName is null AND ColumnName is null) or 
       (ColumnName = @ColumnName)
      )

It's long, ugly, and inefficient, but that's what you need to deal with when implementing an optional filter on a nullable column using a single parameter.

You also could accept two parameters per column that is nullable, like this:

@CreateDate datetime, @CreateDateAll bit

and specify that (for example):

if @CreateDateAll is set to "1", you don't filter on CreateDate (i.e., the @CreateDate param is ignored), otherwise you filter where CreateDate = @CreateDate.

Again, now that we have a clear, logical specification, we can implement it:


where (@CreateDateAll = 1 OR @CreateDate = articleCreateDate OR (@CreateDate is null AND articleCreateDate is null))

still kind of long, but that's the logic.

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 08:29:43

I don't see how you are finding problem in this. I am getting correct output.

-- prepare sample data
declare @t table
(
	cus_name varchar(50),
	cus_city varchar(50),
	cus_country varchar(50)
)

insert into @t
select 'aaa', 'new york', 'US' union all
select 'bbb', 'paris', 'France' union all
select 'ccc', 'bombay', 'India'

declare
	@Cus_Name varchar(30),
	@Cus_City varchar(30),
	@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

-- this query works
select * from @t
WHERE 
	Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
	Cus_City = COALESCE(@Cus_City,Cus_City) AND
	Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 08:48:56

harsh -- if there are null values in the data, they are never returned.

Again, the problem is not code. It is that logically what he is trying to do will not work, no matter how you code it up in T-SQL or any other language.

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 08:55:28

Oh...now I got the problem...it has nothing to do with the values we set for the parameter, but with the table data itself.

How about this then?

-- prepare sample data
declare @t table
(
	cus_name varchar(50),
	cus_city varchar(50),
	cus_country varchar(50)
)

insert into @t
select 'aaa', 'new york', 'US' union all
select NULL, 'paris', NULL union all
select 'ccc', 'bombay', 'India'

declare
	@Cus_Name varchar(30),
	@Cus_City varchar(30),
	@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

-- this query works
select * from @t
WHERE 
	(Cus_Name = COALESCE(@Cus_Name,Cus_Name) or cus_name is NULL) AND
	(Cus_City = COALESCE(@Cus_City,Cus_City) or Cus_City is NULL) AND
	(Cus_Country = COALESCE(@Cus_Country,Cus_Country) or Cus_Country is NULL)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 09:19:33

Again, it is not a coding problem. it is a logic problem. That code will ALWAYS return all NULL values in those columns, even if you pass in a parameter that says "only return 'Paris'".

For the (hopefully) final time: YOU CANNOT WRITE CODE TO "FIX" THIS PROBLEM. The problem is in the specifications themselves, they do not make logical sense and will not work if the column is nullable!!

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 09:28:07

But then there is conflict in requirements. If OP wants only non-null values (which satisfies given condition) to be returned, my first solution will do that. If OP wants NULL values also to be included, my second solution will work.

Now, he has to decide how he wants the output to be.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 09:40:02

>> But then there is conflict in requirements.

That is my ENTIRE point, which I've now repeated quite a few times!

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 09:53:32

But while we are doing all the discussions and suggesting solutions, where is OP? Doesn't even bother to clarify what he wants?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 09:57:31

He is in there, he read my article, asked a question regarding advice on changing his requirements, and I responded. It's all right there, a few posts back.

The problem is, people keep going back and forth trying to write a SQL solution for him, when that is NOT what he needs.

- Jeff
http://weblogs.sqlteam.com/JeffS

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources