Hi,
    I have built a stored procedure for searching.  It works such that
if a value is passed in as parameter, it will only return results based
on that value otherwise it will return everything.  The problem that I
noticed with this yesterday is that when I try to return everything (if
not value) then all the rows with null values are getting truncated.
Any help in this would be appreciated.

Here's my code:

SELECT
    tbl_Case.CaseID,
    tbl_Region.RegionName,
    tbl_City.CityName,
    LastName = (SELECT LastName FROM tbl_Worker WHERE tbl_Worker.LoginID =
tbl_WorkerCase.SSVLoginID),
    FirstName = (SELECT FirstName FROM tbl_Worker WHERE tbl_Worker.LoginID
= tbl_WorkerCase.SSVLoginID),
    tbl_CaseStatus.DateOpened,
    tbl_CaseStatus.DateClosed,
    tbl_CaseStatus.Status,
    tbl_WorkerCase.SSVLoginID,
    tbl_CaseStatus.LastLogDate,
    tbl_Worker.LastName AS LCLastName2,
    tbl_Worker.FirstName AS LCFirstName
FROM
    tbl_Case
    INNER JOIN tbl_CaseClient
        ON tbl_Case.CaseID = tbl_CaseClient.CaseID
    INNER JOIN tbl_CaseStatus
        ON tbl_Case.CaseID = tbl_CaseStatus.CaseID
    LEFT OUTER JOIN tbl_WorkerCase
        ON tbl_Case.CaseID = tbl_WorkerCase.CaseID


    LEFT OUTER JOIN tbl_Worker
        ON tbl_Worker.LoginID = tbl_WorkerCase.LCLoginID

    LEFT OUTER JOIN tbl_Worker WORKER2
        ON tbl_Worker.LoginID = tbl_WorkerCase.PSPLoginID

    LEFT OUTER JOIN  tbl_CaseRegion
        ON tbl_Case.CaseID = tbl_CaseRegion.CaseID
    LEFT OUTER JOIN  tbl_City
        ON  tbl_CaseRegion.CityID =  tbl_City.CityID
    INNER JOIN tbl_Region
        ON tbl_CaseRegion.RegionID = tbl_Region.RegionID
WHERE     tbl_Case.Active = 1
    AND (tbl_CaseStatus.Status IN('Open', 'Re-Opened')
    OR  (tbl_CaseStatus.Status IN('Closed') AND DateAdd(M,6,
tbl_CaseStatus.DateClosed) >= '9/8/2005'))

--THIS IS THE PROBLEM LINE
AND tbl_workerCase.PSPloginID = tbl_workerCase.PSPloginID

AND (tbl_workerCase.PSPloginID = tbl_workerCase.PSPloginID OR
tbl_workerCase.PSPloginID IS NULL)



HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Why would need this condition in the Where clause? What difference between
this condition and "AND1=1"?

Perayu

Show quoteHide quote

Reason for this is to display all the records if there's no search
criteria provided for certain fields.  If there's a search conditions,
I return only the matching rows.

Jens,
        Thanks for responding however there still some strange behavior
that I see.  Here's my condition clause:

WHERE     tbl_Case.Active = 1
    AND (tbl_CaseStatus.Status IN('Open', 'Re-Opened')
    OR  (tbl_CaseStatus.Status IN('Closed') AND DateAdd(M,6,
tbl_CaseStatus.DateClosed) >= @newDate))
    AND
    tbl_Case.CaseID LIKE     CASE
                    WHEN LEN(@NewCaseID) < 1 THEN tbl_Case.CaseID
                    ELSE @NewCaseIDMod
                END
    AND tbl_Region.RegionCode = CASE
                    WHEN LEN(@RegionCode) < 1 THEN tbl_Region.RegionCode
                    ELSE @RegionCode
                END
    AND tbl_CaseRegion.CityID = CASE @CityCode
                    WHEN -1 THEN tbl_CaseRegion.CityID
                    ELSE @CityCode
                END
    AND tbl_CaseClient.FirstName LIKE
                  CASE
                    WHEN LEN(@JMFNameMod) < 1 THEN tbl_CaseClient.FirstName
                    ELSE @JMFNameMod
                END
    AND tbl_CaseClient.LastName LIKE
                  CASE
                    WHEN LEN(@JMLNameMod) < 1 THEN tbl_CaseClient.LastName
                    ELSE @JMLNameMod
                END
    AND
    ( tbl_WorkerCase.LCLoginID = CASE
                    WHEN LEN(@LCCode) < 1 THEN tbl_WorkerCase.LCLoginID
                    ELSE @LCCode
                END
    OR tbl_WorkerCase.LCLoginID = CASE
                    WHEN LEN(@LCCode) < 1 THEN NULL
                    ELSE @LCCode
                END
    )

    AND tbl_CaseStatus.Status = CASE
                    WHEN LEN(@StatusCode) < 1 THEN tbl_CaseStatus.Status
                    ELSE @StatusCode
                END
    /*
    AND

    ( tbl_WorkerCase.PSPLoginID = CASE
                    WHEN LEN(@PSPCode) < 2 THEN tbl_WorkerCase.PSPLoginID
                    ELSE @PSPCode
                END
    OR tbl_WorkerCase.PSPLoginID = CASE
                    WHEN LEN(@PSPCode) < 2 THEN NULL
                    ELSE @PSPCode
                END
    )
    */



There's only one row that has  a value for psploginid and when I
uncomment the last line, I always get one row returned to me instead of
all.

This condition
( tbl_WorkerCase.PSPLoginID = CASE
WHEN LEN(@PSPCode) < 2 THEN tbl_WorkerCase.PSPLoginID
ELSE @PSPCode
END
OR tbl_WorkerCase.PSPLoginID = CASE
WHEN LEN(@PSPCode) < 2 THEN NULL
ELSE @PSPCode
END
)
can be modified as one single line:
( LEN(@PSPCode) < 2 or tbl_WorkerCase.PSPLoginID = @PSPCode).

This condition means if the len(@PSPCode) is greater than 1, then check
tbl_WorkerCase.PSPLoginID = @PSPCode.
You may need to trim the @PSPCode before call Len(), like
Len(rtrim(@PSPCode)).

Perayu


Show quoteHide quote

You may need to check NULL value for @PSPCode as LEN(rtrim(Isnull(@PSPCode,
''))).

Perayu


Show quoteHide quote

Jens,
        Thanks for responding however there still some strange behavior
that I see.  Here's my condition clause:

WHERE     tbl_Case.Active = 1
    AND (tbl_CaseStatus.Status IN('Open', 'Re-Opened')
    OR  (tbl_CaseStatus.Status IN('Closed') AND DateAdd(M,6,
tbl_CaseStatus.DateClosed) >= @newDate))
    AND
    tbl_Case.CaseID LIKE     CASE
                    WHEN LEN(@NewCaseID) < 1 THEN tbl_Case.CaseID
                    ELSE @NewCaseIDMod
                END
    AND tbl_Region.RegionCode = CASE
                    WHEN LEN(@RegionCode) < 1 THEN tbl_Region.RegionCode
                    ELSE @RegionCode
                END
    AND tbl_CaseRegion.CityID = CASE @CityCode
                    WHEN -1 THEN tbl_CaseRegion.CityID
                    ELSE @CityCode
                END
    AND tbl_CaseClient.FirstName LIKE
                  CASE
                    WHEN LEN(@JMFNameMod) < 1 THEN tbl_CaseClient.FirstName
                    ELSE @JMFNameMod
                END
    AND tbl_CaseClient.LastName LIKE
                  CASE
                    WHEN LEN(@JMLNameMod) < 1 THEN tbl_CaseClient.LastName
                    ELSE @JMLNameMod
                END
    AND
    ( tbl_WorkerCase.LCLoginID = CASE
                    WHEN LEN(@LCCode) < 1 THEN tbl_WorkerCase.LCLoginID
                    ELSE @LCCode
                END
    OR tbl_WorkerCase.LCLoginID = CASE
                    WHEN LEN(@LCCode) < 1 THEN NULL
                    ELSE @LCCode
                END
    )

    AND tbl_CaseStatus.Status = CASE
                    WHEN LEN(@StatusCode) < 1 THEN tbl_CaseStatus.Status
                    ELSE @StatusCode
                END
    /*
    AND

    ( tbl_WorkerCase.PSPLoginID = CASE
                    WHEN LEN(@PSPCode) < 2 THEN tbl_WorkerCase.PSPLoginID
                    ELSE @PSPCode
                END
    OR tbl_WorkerCase.PSPLoginID = CASE
                    WHEN LEN(@PSPCode) < 2 THEN NULL
                    ELSE @PSPCode
                END
    )
    */



There's only one row that has  a value for psploginid and when I
uncomment the last line, I always get one row returned to me instead of
all.

Dynamic order by case expression problem
Generating an Index
complicated update (for me)
table update trigger update second table
incremental copy of a database from source to destination