By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,990 Members | 1,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,990 IT Pros & Developers. It's quick & easy.

Optional Where Parameters on Null Data

P: n/a

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

Apr 25 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a


"BillCo" <co**********@gmail.comwrote in message
news:11**********************@u32g2000prd.googlegr oups.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

Apr 25 '07 #2

P: n/a
"BillCo" <co**********@gmail.comwrote in message
news:11**********************@u32g2000prd.googlegr oups.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
Apr 25 '07 #3

P: n/a
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.
Apr 25 '07 #4

P: n/a
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.

Apr 26 '07 #5

P: n/a
On Apr 26, 2:47 pm, --CELKO-- <jcelko...@earthlink.netwrote:
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
May 1 '07 #6

P: n/a
>
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
May 1 '07 #7

P: n/a
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
May 1 '07 #8

P: n/a
BillCo (co**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 1 '07 #9

P: n/a
"BillCo" <co**********@gmail.comwrote in message
news:11*********************@l77g2000hsb.googlegro ups.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
May 1 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.