Optional Where Parameters on Null Data

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

Re: Optional Where Parameters on Null Data

"BillCo" 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
>

Re: Optional Where Parameters on Null Data

"BillCo" 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

Re: Optional Where Parameters on Null Data

BillCo wrote:

> 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.

Try this:

WHERE COALESCE(tblUnits.strUnitID,'') LIKE @strUnitID
etc.

Re: Optional Where Parameters on Null Data

WHERE base_column LIKE (@parameter, '%')

The guy also needs to read anything on ISO-11179 naming conventions so
he does nto use those silly prefixes, knows that there no such thing
as "type_id", etc. But he did get plural and collective table names
right.

Re: Optional Where Parameters on Null Data

On Apr 26, 2:47 pm, --CELKO-- wrote:
> WHERE base_column LIKE (@parameter, '%')
>
> The guy also needs to read anything on ISO-11179 naming conventions so
> he does nto use those silly prefixes, knows that there no such thing
> as "type_id", etc. But he did get plural and collective table names
> right.


Thanks for the ISO reference, I'll look into it - I'm coming from a MS
Access world where those _silly_ naming conventions actually make like
easier. You need to know in vba code at a glance whether it's a
table, query, lookup table - string, int, date - global, local,
modular... whatever - or you'll drive yourself crazy in the head
trying to debug the thing. I've been wondering about appropriate
naming conventions for SQL Server - but haven't as you can tell
bothered doing my research on the subject yet

Bill

Re: Optional Where Parameters on Null Data

>
> I'll pull a Celko and point out you mean columns here. But no matter.
>
I don't know Celko, but I'll watch my language in future ;)

> I don't think you need the WHEN in there, but that's the right basic
> approach.

I'm a little confused by this comment, how to you specify parameters
to a query withoug having either WHEN or HAVING?


> Your nvarchar need sizes, otherwise they're defaulting to one character in
> length.

Yeah, that one took a few minuets of head scratching to debug!


> And do you really want to use LIKE in all of those? It'll really hurt
> performance in most cases.

Really? I'm used to dealing with the MS Jet engine, tips on
optimistaion always appreciated. I was under the (misguided?)
impression that non-numeric searches required a LIKE clause

Anyway, I figured it out in the end - here's the final result. Please
feel free to tear it apart critically - I'm here to learn :)

ALTER PROCEDURE [dbo].[procFindOccupants]
@strOccupant nvarchar(50) = NULL,
@strUnitID nvarchar(50) = NULL,
@strProjectName nvarchar(50) = NULL,
@strAddress nvarchar(50) = NULL,
@strTenancy nvarchar(50) = NULL,
@strTenure nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT qryFullNames.FullName AS [Name],
tblUnits.strUnitID AS [Unit ID],
tblProjects.strProjectName AS [Project],
qryAddresses.Address_OneLine AS [Address],
lkpTenancyTypes.strTenancyType AS [Tenancy],
lkpTenureTypes.strTenureType AS [Tenure]

FROM tblUnits INNER JOIN
tblProjects ON tblUnits.intProjectID = tblProjects.intProjectID
INNER JOIN
tblOccupancies ON tblUnits.strUnitID = tblOccupancies.strUnitID
INNER JOIN
tblOccupants ON tblOccupancies.intOccupantID =
tblOccupants.intOccupantID INNER JOIN
qryFullNames ON tblOccupants.intOccupantID =
qryFullNames.intOccupantID 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 (@strUnitID IS NULL OR tblUnits.strUnitID LIKE
@strUnitID)
AND (@strProjectName IS NULL OR tblProjects.strProjectName LIKE
@strProjectName)
AND (@strAddress IS NULL OR qryAddresses.Address_OneLine LIKE '%'
+ @strAddress + '%')
AND (@strTenancy IS NULL OR lkpTenancyTypes.strTenancyType LIKE
@strTenancy)
AND (@strTenure IS NULL OR lkpTenureTypes.strTenureType LIKE
@strTenure)
AND (@strOccupant IS NULL OR qryFullNames.FullName LIKE
@strOccupant)

END

Re: Optional Where Parameters on Null Data

> I'll pull a Celko and point out you mean columns here. But no matter.

I don't know Celko, but I'll watch my language in future ;)

> Your nvarchar need sizes, otherwise they're defaulting to one character in
> length.

Yeah, that one took a few minuets of head scratching to debug!

> And do you really want to use LIKE in all of those? It'll really hurt
> performance in most cases.

Really? I'm used to dealing with the MS Jet engine, tips on
optimistaion for SQL Server always appreciated. I was under the
(misguided?)
impression that non-numeric character string searches required a LIKE
clause for optimum performance

Anyway, I figured it out in the end - here's the final result. Please
feel free to tear it apart critically - I'm here to learn :)

ALTER PROCEDURE [dbo].[procFindOccupants]
@strOccupant nvarchar(50) = NULL,
@strUnitID nvarchar(50) = NULL,
@strProjectName nvarchar(50) = NULL,
@strAddress nvarchar(50) = NULL,
@strTenancy nvarchar(50) = NULL,
@strTenure nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT
qryFullNames.FullName AS [Name],

tblUnits.strUnitID AS [Unit ID],

tblProjects.strProjectName AS [Project],

qryAddresses.Address_OneLine AS [Address],

lkpTenancyTypes.strTenancyType AS [Tenancy],

lkpTenureTypes.strTenureType AS [Tenure]

FROM tblUnits INNER JOIN
tblProjects ON tblUnits.intProjectID =
tblProjects.intProjectID
INNER JOIN
tblOccupancies ON tblUnits.strUnitID =
tblOccupancies.strUnitID
INNER JOIN
tblOccupants ON
tblOccupancies.intOccupantID =
tblOccupants.intOccupantID INNER JOIN
qryFullNames ON
tblOccupants.intOccupantID =
qryFullNames.intOccupantID 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 (@strUnitID IS NULL OR tblUnits.strUnitID LIKE
@strUnitID)
AND (@strProjectName IS NULL OR
tblProjects.strProjectName LIKE
@strProjectName)
AND (@strAddress IS NULL OR
qryAddresses.Address_OneLine LIKE '%'
+ @strAddress + '%')
AND (@strTenancy IS NULL OR
lkpTenancyTypes.strTenancyType LIKE
@strTenancy)
AND (@strTenure IS NULL OR
lkpTenureTypes.strTenureType LIKE
@strTenure)
AND (@strOccupant IS NULL OR
qryFullNames.FullName LIKE
@strOccupant)

END

Re: Optional Where Parameters on Null Data

BillCo (coleman.bill@gmail.com) writes:
> Anyway, I figured it out in the end - here's the final result. Please
> feel free to tear it apart critically - I'm here to learn :)
>...
> WHERE (@strUnitID IS NULL OR tblUnits.strUnitID LIKE
> @strUnitID)
> AND (@strProjectName IS NULL OR tblProjects.strProjectName LIKE
> @strProjectName)
> ...

While this will work, beware that it will scan the tables every time,
and never use an index. If the data size is modest, this may not be
an issue, but if there are several million rows, it will not be that fun.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Optional Where Parameters on Null Data

"BillCo" wrote in message
news:1178011662.048710.68190@l77g2000hsb.googlegroups.com...
>
>>
>> I'll pull a Celko and point out you mean columns here. But no matter.
>>
> I don't know Celko, but I'll watch my language in future ;)

While Celko can be a bit pedantic (ok... extremely :-) he has somewhat of a
point about making sure to approach problems with the right mindset.

>
>> I don't think you need the WHEN in there, but that's the right basic
>> approach.
>
> I'm a little confused by this comment, how to you specify parameters
> to a query withoug having either WHEN or HAVING?

Hmm, I'd have to review what I was thinking.

>
>
>> Your nvarchar need sizes, otherwise they're defaulting to one character
>> in
>> length.
>
> Yeah, that one took a few minuets of head scratching to debug!

It's a common mistake you'll make once... and then remember forever ;-)

>
>
>> And do you really want to use LIKE in all of those? It'll really hurt
>> performance in most cases.
>
> Really? I'm used to dealing with the MS Jet engine, tips on
> optimistaion always appreciated. I was under the (misguided?)
> impression that non-numeric searches required a LIKE clause
>

Require... only when doing wild card searches.

Problem is it does not allow an index to be used.

Do you really need to match the parameter ANYWHERE in the string?

If not, simply

tblUnits.strUnitID LIKE @strUnitID)

should be

tblUnits.strUnitID = @strUnitID

That will allow SQL Server to use an index. (actually in the above case, I
believe it will anyway, but the = is more proper.)

The problem becomes

qryAddresses.Address_OneLine LIKE '%' + @strAddress + '%'

The leading % completely prevents the use of an index, which will kill
performance.

You may want to rethink that approach.


> Anyway, I figured it out in the end - here's the final result. Please
> feel free to tear it apart critically - I'm here to learn :)
>
> ALTER PROCEDURE [dbo].[procFindOccupants]
> @strOccupant nvarchar(50) = NULL,
> @strUnitID nvarchar(50) = NULL,
> @strProjectName nvarchar(50) = NULL,
> @strAddress nvarchar(50) = NULL,
> @strTenancy nvarchar(50) = NULL,
> @strTenure nvarchar(50) = NULL
> AS
> BEGIN
> SET NOCOUNT ON;
>
> SELECT qryFullNames.FullName AS [Name],
> tblUnits.strUnitID AS [Unit ID],
> tblProjects.strProjectName AS [Project],
> qryAddresses.Address_OneLine AS [Address],
> lkpTenancyTypes.strTenancyType AS [Tenancy],
> lkpTenureTypes.strTenureType AS [Tenure]
>
> FROM tblUnits INNER JOIN
> tblProjects ON tblUnits.intProjectID = tblProjects.intProjectID
> INNER JOIN
> tblOccupancies ON tblUnits.strUnitID = tblOccupancies.strUnitID
> INNER JOIN
> tblOccupants ON tblOccupancies.intOccupantID =
> tblOccupants.intOccupantID INNER JOIN
> qryFullNames ON tblOccupants.intOccupantID =
> qryFullNames.intOccupantID 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 (@strUnitID IS NULL OR tblUnits.strUnitID LIKE
> @strUnitID)
> AND (@strProjectName IS NULL OR tblProjects.strProjectName LIKE
> @strProjectName)
> AND (@strAddress IS NULL OR qryAddresses.Address_OneLine LIKE '%'
> + @strAddress + '%')
> AND (@strTenancy IS NULL OR lkpTenancyTypes.strTenancyType LIKE
> @strTenancy)
> AND (@strTenure IS NULL OR lkpTenureTypes.strTenureType LIKE
> @strTenure)
> AND (@strOccupant IS NULL OR qryFullNames.FullName LIKE
> @strOccupant)
>
> END
>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html