n.value = isNull(@Value,n.Value) Different Record Counts Returned.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » n.value = isNull(@Value,n.Value) Different...

n.value = isNull(@Value,n.Value) Different Record Counts Returned.

Rate Topic

Display Mode

Topic Options

Author

Message

bbrickey-876561

Posted Friday, December 04, 2009 4:35 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 23, 2011 2:34 PM
Points: 24, Visits: 41

We use IsNull in the where clause to dynamically change our returned values. This isn't anything new, its worked reliably for years. Suddenly 2 records are being dropped when the IsNull statement is used, but all are returned when the longer statement.

Anyone have a clue why?

OS & DB Particulars
Windows 2003 SP1 Standard Edition
SQL Server 2005 (9.00.4035.00)

Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Operating System 5.2.3790

--QUERY #1 Records Returned 408 ROWS
--Original Query

DECLARE @STAFFID AS CHAR(12)
SET @STAFFID =null

SELECT n.StaffID,
n.FULL_NAME AS name,
n.TITLE,
o.Office,
n.UpdatedBy,
c.StaffID AS SUPERVISOR_STAFFID
FROM ReadOnlyLinkedServer.crm.dbo.STAFF n
inner join ReadOnlySQLLink.crm.dbo.OFFICE_INFO o
ON n.OFFICE = o.OFFICEID
LEFT outer JOIN dbo.Rep a
ON n.STAFFID = a.STAFFID
LEFT OUTER JOIN ReadOnlyLinkedServer.crm.dbo.STAFF c
ON a.AssignedSupervisorID = c.STAFFID
WHERE (n.STAFF_TYPE IS NOT NULL AND n.STAFF_TYPE NOT LIKE '%TEMP')
AND b.HC_STAFFID = isnull(@HC_STAFFID, b.HC_STAFFID)
ORDER BY n.FULL_NAME

--Query #2 Records returned 410 ROWS
--Revised Query

SELECT n.StaffID,
n.FULL_NAME AS name,
n.TITLE,
o.Office,
n.UpdatedBy,
c.StaffID AS SUPERVISOR_STAFFID
FROM ReadOnlyLinkedServer.crm.dbo.STAFF n
inner join ReadOnlySQLLink.crm.dbo.OFFICE_INFO o
ON n.OFFICE = o.OFFICEID
LEFT outer JOIN dbo.Rep a
ON n.STAFFID = a.STAFFID
LEFT OUTER JOIN ReadOnlyLinkedServer.crm.dbo.STAFF c
ON a.AssignedSupervisorID = c.STAFFID
WHERE (n.STAFF_TYPE IS NOT NULL AND n.STAFF_TYPE NOT LIKE '%TEMP')
AND ((@STAFFID IS NOT NULL AND n.StaffID = @STAFFID) OR ( @STAFFID IS NULL AND n.STAFFID = n.STAFFID))
ORDER BY n.FULL_NAME

Thanks,

Brodie

Post #829308

SQLRNNR

Posted Sunday, December 06, 2009 4:55 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

Should the line:

AND b.HC_STAFFID = isnull(@HC_STAFFID, b.HC_STAFFID)

be as is, or should it be the following?

AND b.HC_STAFFID = isnull(@STAFFID, b.HC_STAFFID)

I don't see a variable declared for @HC_StaffID.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #829577

bbrickey-876561

Posted Sunday, December 06, 2009 11:19 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 23, 2011 2:34 PM
Points: 24, Visits: 41

I actually cleaned up the query to make it more generic it should have been just like the other.

AND n.STAFFID = isnull(@STAFFID, n.STAFFID)

The main question becomes why the IsNull function doesn't show the same results as a longer statement that tests for the same thing.

Post #829657

SQLRNNR

Posted Monday, December 07, 2009 1:27 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

Please post sample data and table schema.

The two should return the same results. It seems that some data may be amiss, or maybe a setting in the linkedserver setup.

It looks like you are joining to two linkedservers (one being a SQLLink 3rd party app) and a CRM database. Has anything changed in the settings for the linkedservers?

Post #830235

Timothy J Hartford

Posted Tuesday, December 08, 2009 9:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, Visits: 502

Just a thought, but NULL != NULL (unless ANSI_NULLS setting is OFF), therefore if the value in the column is null, this would return false.

Post #830804

« Prev Topic | Next Topic »

Permissions