Solved

SQL Search algorithm

Posted on 2003-10-25
9
275 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
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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now