SQL Search algorithm

Scenario:
Picture a search page where I'm searching for a person record in a database, then returning the results.
The search page contains, say 5 textfields, and a button labeled "Go".
The text fields represent:
- First Name
- Last Name
- City
- State
- Country

So the user of this page could enter only the last name, or maybe first name and last name. Basically they could
fill out all fields, one field, no fields, or some combination of fields.

This means that when the page is submitted, I can determine that each field either has a value assigned to it, or doesn't.
So I create a Stored Procedure that looks like the following:

CREATE PROCEDURE sp_SearchPersons
(@FirstName varchar(50) = null,
 @LastName varchar(50) = null,
 @City varchar(50) = null,
 @State varchar(2) = null,
 @Country varchar(3) = null)
AS

-- The case where all fields have a value
IF (@FirstName IS NOT NULL AND @LastName IS NOT NULL AND @City IS NOT NULL AND @State IS NOT NULL AND @Country IS NOT NULL)
BEGIN
       SELECT *
       FROM Persons
       WHERE FirstName LIKE @FirstName + '%'
           AND LastName LIKE @LastName + '%'
           AND City LIKE @City + '%'
           AND State = @State
           AND Country = @Country
END

----------------------------------------------------------------------------------------------------------
Then I would conditionally check for all possible combinations of field values.

This works, and works well, however it quickly becomes unmanageable.
For the above examples there would 2^5 possible combinations, or 32 IF Blocks.
Add another field and now I'm at 2^6 or 64 IF Blocks.

There has to be a better way.

Anyone got any suggestions?

Thanks,
Seeflat

LVL 5
seeflatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

seeflatAuthor Commented:
I also realize that this question might be better posed in the SQL group, but I thought I would give my fellow C# developers a crack at it first.
0
AlexFMCommented:
Maybe this SQL is better to generate dynamically in C# code. SQL is not full-featured programming language and in this case you can break all design rules and write SQL in the program.
0
seeflatAuthor Commented:
Good suggestion, however I'd thought about that, and while it would work, I want to gain the performance advantages of using stored procedures. The DBMS optimizes the executiion of stored procs, while using ad-hoc queries is only as good as the tables indicies, and puts an unwanted hit on the DB everytime it's executed. This database has the potential to grow quite large, and I want to stick to best practices as much as I can.

Thanks AlexFM.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

atteveldtCommented:
Why not try something like:

CREATE PROCEDURE sp_SearchPersons
(@FirstName varchar(50) = null,
 @LastName varchar(50) = null,
 @City varchar(50) = null,
 @State varchar(2) = null,
 @Country varchar(3) = null)
AS
       SELECT *
       FROM Persons
       WHERE (FirstName LIKE @FirstName + '%' OR FirstName IS NULL)
           AND (LastName LIKE @LastName + '%' OR LastName IS NULL)
           AND (City LIKE @City + '%' OR City IS NULL)
           AND (State = @State OR State IS NULL)
           AND (Country = @Country OR Country IS NULL)

If your dbase is MSSQL, then an inline table valued function might be even more efficient. For dumber dbases do not put to IS NULL to the left as expressions are evaluated right-to-left in ANSI SQL.

Bas.
0
seeflatAuthor Commented:
Thanks Bas.
But at first glance, it doesn't do what I want.

What you suggested will only test whether the field's value in the database is null. Which all these fields (for sake of argument) cannot allow nulls for their values.
So effectively the { OR field IS NULL } statement(s) will never affect the query results, because they will always evaluate to false.

Also, I am using MSSQL 2k.

Could you elaborate of the "inline table valued function" you mentioned?

Thanks,
seeflat
0
RadimHampelCommented:
Try this:

Where
  IsNull(FirstName, '') Like IsNull(@FirstName, IsNull(FirstName, ''))
  AND ...
  AND IsNull(Country, '') = IsNull(@Country, IsNull(Country, ''))

This is working solution, and if you don't have millions of rows in db, it will be fast enough.

Radim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
seeflatAuthor Commented:
Thanks Radim, I've give it a shot.

seeflat
0
seeflatAuthor Commented:
Radim,

It works GREAT!
However, I don't like using solutions I don't fully understand.

If you have don't mind, could you explain what it's doing?

How does IsNull() Like IsNull(@, IsNull()) work?
I know what IsNull does, but can't figure out how this is all working together.

Thanks very much,
seeflat

0
RadimHampelCommented:
Point is, when column value is null, then expressions like
Column = IsNull(@Var, Column) are in fact: Null = Null, which is obviously false.

But using IsNull(Column, 'some value') = IsNull(@Var, IsNull(Column, 'some value) leads to(if @var is null): IsNull(Column, 'some value') = IsNull(Column, 'some value') what is 'some value' = 'Some value', and this is true.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.