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

> 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 ;)

> 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 for SQL Server always appreciated. I was under the
(misguided?)
impression that non-numeric character string searches required a LIKE
clause for optimum performance

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]