SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
bbrickey-876561
bbrickey-876561
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67611 Visits: 18570
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

bbrickey-876561
bbrickey-876561
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67611 Visits: 18570
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

Timothy J Hartford
Timothy J Hartford
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1024 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search