Strange behaviour with NULLs - 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 2000 Forums

SQL Server Development (2000)

Strange behaviour with NULLs

Author

Topic

seantynan
Starting Member

1 Post

Posted - 2001-11-16 : 07:32:42

Hi all,

I have been writing stored procedures that use custom where clauses of the form:

SELECT DISTINCT
dbo.tbl_Products.vcProductName,
FROM dbo.tbl_Products
WHERE fk_intClientProducer_ID = isnull(@intProducerID, fk_intClientProducer_ID)

as described in http://www.aspalliance.com/stevesmith/articles/spcustomwhere.asp.

This technique encounters a problem when both the column fk_intClientProducer_ID and the parameter @intProducerID are NULL.

The WHERE clause then resolves to:

WHERE fk_intClientProducer_ID = NULL

Of course with ANSI_NULLS set to ON, this returns FALSE.
The problem that I have is that it also returns FALSE when ANSI_NULLS is set to OFF.
After a bit of testing, I discovered the following strange behaviour...

If I compile a test stored procedure in query analyser:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE Procedure _TestNulls

As
/*
This sp must be compiled in query analyser with ANSI_NULLS set to off.
*/

if ( isnull(null,null) = isnull(null,null))
select '1: true'
else
select '1: false'

if ( null = null)
select '2: true'
else
select '2: false'
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The results are:

------------
1: false
(1 row(s) returned)

------------
2: true
(1 row(s) returned)
No more results.
@RETURN_VALUE = 0
Finished running CCoI_lgn."_TestNulls".

In other words, with ANSI_NULLS set to off,

isnull(null,null) = isnull(null,null) returns false

but
null = null returns true!

Can anyone explain this behaviour?
I had expected that the two if statements would return the same result, since isnull(null, null) should return NULL, therefore making the two statements equivalent.

Thanks in advance,

Sean Tynan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources