|
|
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
Navigation:
[Reply to this message]
|