December 4, 2009 at 4:35 pm
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
December 6, 2009 at 4:55 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 6, 2009 at 11:19 pm
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.
December 7, 2009 at 1:27 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2009 at 9:52 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply