Ansi NUll Issue??

Last Post 10 Dec 2009 12:03 PM by ASAPDBA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ASAPDBA
New Member
New Member

--
13 Aug 2009 08:59 AM
We are in the process of testing our application after upgrading from SQL 2000 Standard to SQL 2008 Standard all default instalation settings. We have come across an issue with some logic in a proc that behaves differently between the two. I have shortened the query down for readability.

The server seems to handle nulls in a where clause differently based on the query. It didn't matter what the Ansi_null value was, on the server instance or the database. (I believe I hit all combinations.)


SET ANSI_NULLS OFF
--SET ANSI_NULLS ON
SET NOCOUNT ON
DECLARE @PId Int, @OId Int, @FID int, @sysDate DateTime

SELECT @PId = 509, @OId = 1, @FID = 2, @SysDate = getdate()

--First query the where is commented out and it returns three rows, verified the data is correct.
SELECT p.PId, cc.FID, @FID as '@FID'
FROM dbo.P p
Inner Join dbo.PA a on p.PId = a.PId
Inner Join dbo.ufnTopPEA(@PId, Null) as e on p.PId = e.PId and e.OFlag = 1
Inner Join dbo.ufnTopEPS (@PId, @sysDate) teps on p.PID = teps.PID
LEFT join dbo.ufnTopECCA (@PID, null , 1 , @sysDate ) as utecca on p.PID = utecca.PID
LEFT join dbo.CC as cc on utecca.CCNID = cc.CCNID
--WHERE (ISNULL(@FID,0) = 0 or (@FID = 1 and cc.FID IN (1,3)) or (@FID = 2 and cc.FID = 2))

/*
PId FID @FID
----------- ----------- -----------
509 NULL 2
509 NULL 2
509 NULL 2
*/
-- second query the where is uncommented and the logic that works fine in 2000 is letting these three
--records through and they shouldn't be.

SELECT p.PId, cc.FID, @FID as '@FID'
FROM dbo.P p
Inner Join dbo.PA a on p.PId = a.PId
Inner Join dbo.ufnTopPEA(@PId, Null) as e on p.PId = e.PId and e.OFlag = 1
Inner Join dbo.ufnTopEPS (@PId, @sysDate) teps on p.PID = teps.PID
LEFT join dbo.ufnTopECCA (@PID, null , 1 , @sysDate ) as utecca on p.PID = utecca.PID
LEFT join dbo.CC as cc on utecca.CCNID = cc.CCNID
WHERE (ISNULL(@FID,0) = 0 or (@FID = 1 and cc.FID IN (1,3)) or (@FID = 2 and cc.FID = 2))

/*
PId FID @FID
----------- ----------- -----------
509 NULL 2
509 NULL 2
509 NULL 2
*/
-- third query, while reducing the query size I ran into commenting this line out, the where clause works
--(and should have no effect on the where clause)

SELECT p.PId, cc.FID, @FID as '@FID'
FROM dbo.P p
Inner Join dbo.PA a on p.PId = a.PId
Inner Join dbo.ufnTopPEA(@PId, Null) as e on p.PId = e.PId and e.OFlag = 1
--Inner Join dbo.ufnTopEPS (@PId, @sysDate) teps on p.PID = teps.PID
LEFT join dbo.ufnTopECCA (@PID, null , 1 , @sysDate ) as utecca on p.PID = utecca.PID
LEFT join dbo.CC as cc on utecca.CCNID = cc.CCNID
WHERE (ISNULL(@FID,0) = 0 or (@FID = 1 and cc.FID IN (1,3)) or (@FID = 2 and cc.FID = 2))
/*
PersonId FLSAID @FLSAID
----------- ----------- -----------
*/

--Forth query just ran to ensure that join didn't effect the data.
SELECT p.PId, cc.FID, @FID as '@FID'
FROM dbo.P p
Inner Join dbo.PA a on p.PId = a.PId
Inner Join dbo.ufnTopPEA(@PId, Null) as e on p.PId = e.PId and e.OFlag = 1
--Inner Join dbo.ufnTopEPS (@PId, @sysDate) teps on p.PID = teps.PID
LEFT join dbo.ufnTopECCA (@PID, null , 1 , @sysDate ) as utecca on p.PID = utecca.PID
LEFT join dbo.CC as cc on utecca.CCNID = cc.CCNID
--WHERE (ISNULL(@FID,0) = 0 or (@FID = 1 and cc.FID IN (1,3)) or (@FID = 2 and cc.FID = 2
SQLUSA
New Member
New Member

--
04 Dec 2009 08:16 AM
You may consider using ANSI COALESCE instead of ISNULL.

Let us know what happens.

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com
ASAPDBA
New Member
New Member

--
10 Dec 2009 12:03 PM
This issue disappeared after we installed SP1.
You are not authorized to post a reply.

Acceptable Use Policy