Solved

SQL Search algorithm

Posted on 2003-10-25
9
284 Views
Last Modified: 2012-05-04
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

0
Comment
Question by:seeflat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Author Comment

by:seeflat
ID: 9619027
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
 
LVL 48

Expert Comment

by:AlexFM
ID: 9620116
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
 
LVL 5

Author Comment

by:seeflat
ID: 9620700
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:atteveldt
ID: 9620896
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
 
LVL 5

Author Comment

by:seeflat
ID: 9623467
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
 
LVL 1

Accepted Solution

by:
RadimHampel earned 500 total points
ID: 9625360
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
 
LVL 5

Author Comment

by:seeflat
ID: 9628077
Thanks Radim, I've give it a shot.

seeflat
0
 
LVL 5

Author Comment

by:seeflat
ID: 9640449
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
 
LVL 1

Expert Comment

by:RadimHampel
ID: 9640863
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question