Posted by BillCo on 04/25/07 07:38

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

[Back to original message]