469,167 Members | 1,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Lots of queries for my db

Hello all,

I have a database in SQL Server that should save data from a CRM-like
application.

The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.

This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.

If anyone can help, please.
Thank you in advance.

Regards,

Freek Versteijn
Jul 20 '05 #1
3 1622
This seems like the article for you:
http://www.algonet.se/~sommar/dyn-search.html

--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=d...blic.sqlserver
"Versteijn" <ve*******@538mail.nl> wrote in message
news:4d**************************@posting.google.c om...
Hello all,

I have a database in SQL Server that should save data from a CRM-like
application.

The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.

This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.

If anyone can help, please.
Thank you in advance.

Regards,

Freek Versteijn

Jul 20 '05 #2

One of the solutions is not a technical solution : it's a deeper analysis.
A great advantage of this solution is that the technical implementation will
be, we hope, simpler than an exhaustive research across all the columns of
the database.

The risk of an exhaustive implementation is an research that the users
will use only at 5% of its capacities (always quering on the 4 same
columns...).

I had implemented this kind of research program : it worked fine when it
returned 2 result lines, but too much often, it had returned thousand lines
!

For information, the main algorithm was (if my memory do not fail !) :
- ask to the user the value to search, and for string value only, if it is
an exact search or an "LIKE %s%" search ;
- detection of the type of value to search (char, interger, float, ...) ;
- research of the columns of this type (1 query in
INFORMATION_SCHEMA.COLUMNS) ;
- for each table with at least one matched column, 1 SELECT query :
- SELECT PK, fld1, fld2
- FROM the table
- WHERE fld1='value' OR fld2='value' OR ...
(or WHERE fld1 LIKE '%value%' OR ...)

* analyse the result recordset and display the results in a array, with
these columns : TableName, PKValueOfTheMatchedRecord, FieldName, FieldValue.

This program gives the right results, but it talks about "Table", "PK",
"Field", ... and it's not the user's language. So, the difficulty is to
transform these "data oriented" results in "users oriented" results !

I'm interested in an different approach.

Jacques.
"Versteijn" <ve*******@538mail.nl> a écrit dans le message de news:
4d**************************@posting.google.com...
Hello all,

I have a database in SQL Server that should save data from a CRM-like
application.

The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.

This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.

If anyone can help, please.
Thank you in advance.

Regards,

Freek Versteijn

Jul 20 '05 #3
> The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.


Hi,

Without knowing the details about your DB, the following should work.
Assuming that there are no nulls in your key fields and they are set
as varchar/char. If they're integer, the query will do an implicit
conversion. Of course, the hard part is actually testing and
optimizing the queries. -- Louis

-- All the parameters should default to '%'
(to return all rows, unless the user passes a value)

select *
from mytable
where
customerID like @customerID
and customerID like '%'+ @customerIDfragment +'%'
and customername like @customername
and customername like '%'+ @customernamefragment +'%'
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

44 posts views Thread by Greg Strong | last post: by
6 posts views Thread by Jack | last post: by
5 posts views Thread by listerofsmeg01 | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.