Posted by Greg D. Moore \(Strider\) on 04/25/07 11:59

"BillCo" <coleman.bill@gmail.com> wrote in message
news:1177486737.550796.129330@u32g2000prd.googlegroups.com...
>
> 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]