Posted by BillCo on 05/01/07 09:27

>
> I'll pull a Celko and point out you mean columns here. But no matter.
>
I don't know Celko, but I'll watch my language in future ;)

> I don't think you need the WHEN in there, but that's the right basic
> approach.

I'm a little confused by this comment, how to you specify parameters
to a query withoug having either WHEN or HAVING?


> Your nvarchar need sizes, otherwise they're defaulting to one character in
> length.

Yeah, that one took a few minuets of head scratching to debug!


> And do you really want to use LIKE in all of those? It'll really hurt
> performance in most cases.

Really? I'm used to dealing with the MS Jet engine, tips on
optimistaion always appreciated. I was under the (misguided?)
impression that non-numeric searches required a LIKE clause

Anyway, I figured it out in the end - here's the final result. Please
feel free to tear it apart critically - I'm here to learn :)

ALTER PROCEDURE [dbo].[procFindOccupants]
@strOccupant nvarchar(50) = NULL,
@strUnitID nvarchar(50) = NULL,
@strProjectName nvarchar(50) = NULL,
@strAddress nvarchar(50) = NULL,
@strTenancy nvarchar(50) = NULL,
@strTenure nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT qryFullNames.FullName AS [Name],
tblUnits.strUnitID AS [Unit ID],
tblProjects.strProjectName AS [Project],
qryAddresses.Address_OneLine AS [Address],
lkpTenancyTypes.strTenancyType AS [Tenancy],
lkpTenureTypes.strTenureType AS [Tenure]

FROM tblUnits INNER JOIN
tblProjects ON tblUnits.intProjectID = tblProjects.intProjectID
INNER JOIN
tblOccupancies ON tblUnits.strUnitID = tblOccupancies.strUnitID
INNER JOIN
tblOccupants ON tblOccupancies.intOccupantID =
tblOccupants.intOccupantID INNER JOIN
qryFullNames ON tblOccupants.intOccupantID =
qryFullNames.intOccupantID LEFT OUTER JOIN
lkpTenancyTypes ON tblUnits.intTenancyType =
lkpTenancyTypes.intTenancyType LEFT OUTER JOIN
lkpTenureTypes ON tblUnits.intTenureType =
lkpTenureTypes.intTenureTypeID LEFT OUTER JOIN
qryAddresses ON tblUnits.strUnitID = qryAddresses.strUnitID

WHERE (@strUnitID IS NULL OR tblUnits.strUnitID LIKE
@strUnitID)
AND (@strProjectName IS NULL OR tblProjects.strProjectName LIKE
@strProjectName)
AND (@strAddress IS NULL OR qryAddresses.Address_OneLine LIKE '%'
+ @strAddress + '%')
AND (@strTenancy IS NULL OR lkpTenancyTypes.strTenancyType LIKE
@strTenancy)
AND (@strTenure IS NULL OR lkpTenureTypes.strTenureType LIKE
@strTenure)
AND (@strOccupant IS NULL OR qryFullNames.FullName LIKE
@strOccupant)

END

[Back to original message]