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

  • 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

  • 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

  • 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.

  • 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

  • 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