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

"BillCo" <coleman.bill (AT) gmail (DOT) com> wrote

"BillCo" <coleman.bill (AT) gmail (DOT) com> wrote

We'll try not to be TOO mean.


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


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.



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

BillCo wrote:

Try this:

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

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.

On Apr 26, 2:47 pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

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

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


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


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

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

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

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

BillCo (coleman.bill (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

"BillCo" <coleman.bill (AT) gmail (DOT) com> wrote

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.

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

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

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.


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