Lots of queries for my db

[Versteijn]

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

[Tibor Karaszi]

Re: Lots of queries for my db

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=djq&as_ugroup=microsoft.public.sqlserver

"Versteijn" <versteijn@538m ail.nl> wrote in message
news:4d19834f.0 311130127.2a2c9 eda@posting.goo gle.com...[color=blue]
> 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[/color]

[VUILLERMET Jacques]

Re: Lots of queries for my db


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_SCH EMA.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, PKValueOfTheMat chedRecord, 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" <versteijn@538m ail.nl> a écrit dans le message de news:
4d19834f.031113 0127.2a2c9eda@p osting.google.c om...[color=blue]
> 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[/color]

[louis nguyen]

Re: Lots of queries for my db

> The database consists of tables like products, services, customers,[color=blue]
> 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.[/color]

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 '%'+ @customerIDfrag ment +'%'
and customername like @customername
and customername like '%'+ @customernamefr agment +'%'