Null comparison question

Question

Null comparison question

Vishwas Apte

Old Hand

Points: 309
More actions
October 12, 2005 at 3:51 pm

#169146

Trying to compare a column with a column does not return records when both column values are null.
Here is what I am trying to do inside a stored procedure.
SET ANSI_NULLS OFF
GO
Create Procedure GetFileObjects As
Begin
Select ObjectID From ObjectTable Where ObjectName=FilePathName
End
GO
This returns records only when ObjectName and FilePathName values are non-null and same.
I want to return all matching records as well as records with both columns set to null and I thought SET ANSI_NULLS OFF will do it for me. I am using SQL-Server 2000. Any clues???

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The "SET ANSI_NULLS OFF" needs to be specified as a run time setting, and does not need to be set at creation time of the stored procedure. Just move the "SET ANSI_NULLS OFF" to within the sp source

Create Procedure GetFileObjects As

Begin

SET ANSI_NULLS OFF

Select ObjectID From ObjectTable Where ObjectName=FilePathName

End

GO

SQL = Scarcely Qualifies as a Language

Vishwas Apte Old Hand Points: 309 More actions · Answer 2

Thanks Carl for the reply. But still does not work.

Here is a my test in SQL Server 2000 Query Analyser

-- Warning !!!, dropping a table

Drop Table T1

Create Table T1 (C1 int, C2 int, C3 int default 0)

GO

Insert Into T1 (C1, C2) Values (1, 2)

Insert Into T1 (C1, C2) Values (1, 1)

Insert Into T1 (C1, C2) Values (null, null)

Insert Into T1 (C1, C2) Values (1, null)

GO

Drop Procedure TestNulls

GO

SET ANSI_NULLS OFF

GO

Create Procedure TestNulls

As

Begin

SET ANSI_NULLS OFF

Select * from T1 Where C1=C2

End

GO

Exec TestNulls

Returns only one record , with C1 and C2 as 1

I am missing some other SET option???

DanielG-259128 SSC Veteran Points: 291 More actions · Answer 3

Hi.

Why not use the Isnull() statement ?

Select * from T1 Where Isnull(C1,0)=Isnull(C2,0)

You can usually not compare a Null to a value, as technically it is not actually a value.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Rather don't use the ansi_nulls setting and write your stored proc as follows

Create Procedure TestNulls

As

Begin

Select * from T1 Where (C1=C2 OR (C1 IS NULL AND C2 IS NULL))

End

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

Try this link for more about NULL handling in SQL Server. Hope this helps.

http://www.akadia.com/services/dealing_with_null_values.html

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

mark baekdal-145375 SSCrazy Points: 2200 More actions · Answer 6

we use a standard method:

Select ObjectID

From ObjectTable

Where 0 = case when ObjectName=FilePathName then 0

when ObjectName is null and FilePathName is null then 0

else 1 end

regards,

Mark Baekdal

MSN m_baekdal@hotmail.com

+44 (0)141 416 1490

+44 (0)208 241 1762

http://www.dbghost.com

http://www.innovartis.co.uk

Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

Lutz Albers SSC Rookie Points: 46 More actions · Answer 7

Technically speaking: SELECT returns all rows where the WHERE condition evaluates to TRUE. A "="comparision with a column containing a NULL is UNDEFINED, so the SELECT never uses that ROW.