Solved

SQL Search algorithm

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

 

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

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!

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 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