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

"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 :)
>

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

[Back to original message]