Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

n.value = isNull(@Value,n.Value) Different Record Counts Returned. Expand / Collapse
Author
Message
Posted Friday, December 4, 2009 4:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 1:11 PM
Points: 24, Visits: 43
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
Posted Sunday, December 6, 2009 4:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:55 PM
Points: 21,204, Visits: 14,889
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


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
Posted Sunday, December 6, 2009 11:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 1:11 PM
Points: 24, Visits: 43
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
Posted Monday, December 7, 2009 1:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:55 PM
Points: 21,204, Visits: 14,889
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?




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


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 #830235
Posted Tuesday, December 8, 2009 9:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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 »

Add to briefcase

Permissions Expand / Collapse