Optional Where Parameters on Null Data
- From: BillCo <coleman.bill@xxxxxxxxx>
- Date: 25 Apr 2007 00:38:57 -0700
I'm new to SQL Server, so if I'm doing anything stupid don't be
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.
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'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
.
- Follow-Ups:
- Re: Optional Where Parameters on Null Data
- From: Ed Murphy
- Re: Optional Where Parameters on Null Data
- From: Greg D. Moore \(Strider\)
- Re: Optional Where Parameters on Null Data
- From: Greg D. Moore \(Strider\)
- Re: Optional Where Parameters on Null Data
- Prev by Date: Re: Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K
- Next by Date: Re: Update trigger or ?
- Previous by thread: sql script
- Next by thread: Re: Optional Where Parameters on Null Data
- Index(es):
Relevant Pages
|
