Searching NULL fields - SQL Server Forums

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)

Searching NULL fields

Author

Topic

GoodTogether
Starting Member

5 Posts

Posted - 2007-12-13 : 10:16:32

Hi,

I have a search on a website that has some optional values. Each of these values are set up as parameters on a stored procedure. If a user doesn't enter a vlaue on the search form I pass a null value for the relevant parameter in to the stored proc.

Within my stored proc is use CAOLESCE as shown below to search the database which allows for null values to be passed in for the parameters:


CREATE PROCEDURE [dbo].[uspItems_AdvancedSearch]
  @Param1 int,
  @Param2 int
AS
SELECT
  i.[Value1],
  i.[Value2],
  i.[Value3]
FROM
   [Items] i
WHERE
   i.[Value1] = COALESCE(@Param1 ,i.[Value1])
   AND
   i.[Value2] = COALESCE(@Param2 ,i.[Value2])

My problem is when a column itself contains a null value, that column is not returned in my results.

I can see where the problem lies and that is COALESCE won't return anything for([null param], [null column])

Any ideas how I can work around this?

Basically, if a column is null and the parameter is null that row should be returned. If the parameter has a value then only matching rows should be returned.

Hope I explained that well enough!

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 10:26:01

[code]where
(
@Param1 is null
or i.Value1 = @Param1
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 10:51:09

read this:

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

your problem is not coding, and it isn't T-SQL, it's that your specs don't make sense.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-13 : 10:53:55

try this also:-

WHERE
ISNULL(i.[Value1],'') = ISNULL(@Param1 ,ISNULL(i.[Value1],''))
AND
ISNULL(i.[Value2],'') = ISNULL(@Param2 ,ISNULL(i.[Value2],''))

JasonL
Starting Member

35 Posts

Posted - 2007-12-13 : 15:08:15

NULLs are no fun :) :)

see http://www.codeproject.com/KB/database/sqldodont.aspx pretty good write-up too see section "DO Avoid NULLable columns"

Jeff>> Good work on "Sometimes the problem isn't the code. It's the specs"

(FYI: I work at Microsoft as an ISV Architect Evangelist)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 15:18:57

Thanks, Jason, I appreciate it !

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources