Re: Optional Where Parameters on Null Data
- From: "Greg D. Moore \(Strider\)" <mooregr_deleteth1s@xxxxxxxxxxx>
- Date: Wed, 25 Apr 2007 12:01:35 GMT
"BillCo" <coleman.bill@xxxxxxxxx> wrote in message
news:1177486737.550796.129330@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm new to SQL Server, so if I'm doing anything stupid don't be
mean :)
We'll try not to be TOO mean.
I have a procedure that I use to return data based on optional
parameters. It works fine, except when the underlying data contains a
null on one if the fields being searched.
I'll pull a Celko and point out you mean columns here. But no matter.
My system uses a default wildcard for all parameters, so this excludes
such records. I need a way to add in " OR fldName IS NULL " where the
parameter is empty or '%'. I've looked at using CASE WHEN, but it
doesnt seem to like SQL Keywords being part of the WHEN clause.
I don't think you need the WHEN in there, but that's the right basic
approach.
However, a couple of things:
Your nvarchar need sizes, otherwise they're defaulting to one character in
length.
And do you really want to use LIKE in all of those? It'll really hurt
performance in most cases.
I'd hate to have to resort to executing concatonated strings made from
IF and ELSE statements. Just too messy and not at all pretty!
Any Ideas? Here's what I've got:
ALTER PROCEDURE [dbo].[procFindUnits]
@strUnitID nvarchar = '%',
@strProjectName nvarchar = '%',
@strAddress nvarchar = '%',
@strTenancy nvarchar = '%',
@strTenure nvarchar = '%'
AS
BEGIN
SET NOCOUNT ON;
SELECT tblUnits.strUnitID,
tblProjects.strProjectName,
qryAddresses.Address_OneLine,
lkpTenancyTypes.strTenancyType,
lkpTenureTypes.strTenureType
FROM tblUnits INNER JOIN
tblProjects ON tblUnits.intProjectID = tblProjects.intProjectID
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 (tblUnits.strUnitID LIKE @strUnitID)
AND (tblProjects.strProjectName LIKE @strProjectName)
AND (qryAddresses.Address_OneLine LIKE @strAddress)
AND (lkpTenancyTypes.strTenancyType LIKE @strTenancy)
AND (lkpTenureTypes.strTenureType LIKE @strTenure)
END
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
.
- References:
- Optional Where Parameters on Null Data
- From: BillCo
- Optional Where Parameters on Null Data
- Prev by Date: Re: Optional Where Parameters on Null Data
- Next by Date: Upgrade
- Previous by thread: Re: Optional Where Parameters on Null Data
- Next by thread: Re: Optional Where Parameters on Null Data
- Index(es):
Relevant Pages
|
