|
|
Posted by Greg D. Moore \(Strider\) on 05/01/07 13:06
"BillCo" <coleman.bill@gmail.com> wrote in message
news:1178011662.048710.68190@l77g2000hsb.googlegroups.com...
>
>>
>> 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 ;)
While Celko can be a bit pedantic (ok... extremely :-) he has somewhat of a
point about making sure to approach problems with the right mindset.
>
>> 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?
Hmm, I'd have to review what I was thinking.
>
>
>> 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!
It's a common mistake you'll make once... and then remember forever ;-)
>
>
>> 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
>
Require... only when doing wild card searches.
Problem is it does not allow an index to be used.
Do you really need to match the parameter ANYWHERE in the string?
If not, simply
tblUnits.strUnitID LIKE @strUnitID)
should be
tblUnits.strUnitID = @strUnitID
That will allow SQL Server to use an index. (actually in the above case, I
believe it will anyway, but the = is more proper.)
The problem becomes
qryAddresses.Address_OneLine LIKE '%' + @strAddress + '%'
The leading % completely prevents the use of an index, which will kill
performance.
You may want to rethink that approach.
> 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
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Navigation:
[Reply to this message]
|