Posted by John on 06/07/06 14:26

Unfortunately, the client supply the where clause, which could be
anything, such as
(Lastname='johnson' or firstname='smith') and (city='Los
Angeles') or productid in (234,23434,33) ....
So it is very hard for me to break the search criteria into different
part and do a union considering the different logical operator
combination

Erland Sommarskog wrote:
> John (johnxhc@yahoo.com) writes:
> > (Using SQL2005) I need to design the generic search utility on the
> > database server (run as Web Service), client supply the search criteria
> > (where clause)
>
> The WHERE clause is not coming as a parameter, I hope?
>
> > 2) Substitute the where clause with subquery . for example, if the
> > where clause is following:
> >
> > where subtable1.name ='john' or subtable2.product_code ='xyz'
> > or subtable1.name ='tom
> >
> > I will replace it with the following : (please trust me I have the code
> > to do the Substitution but it will be too hard to explain here)
> >
> > Select distinct maintable.id where
> > exists (select * from subttable1 where name = 'john' and
> > maintable.id=subtable1.id)
> > or
> > exists (select * from subttable2 where product_code = 'xyz' and
> > maintable.id=subtable2.id)
> > or
> > exists (select * from subttable1 where name = 'tom' and
> > maintable.id=subtable1.id)
> > order by ...
>
> So, the rule is that if any of the criterias match, the row should
> be returned. Often dynamic searches are the other way round: all
> should match.
>
> One thing you could try is:
>
> Select maintable.id
> where exists (select * from subttable1 where name = 'john' and
> maintable.id=subtable1.id)
> union
> Select maintable.id
> exists (select * from subttable2 where product_code = 'xyz' and
> maintable.id=subtable2.id)
> union
> Select maintable.id
> exists (select * from subttable1 where name = 'tom' and
> maintable.id=subtable1.id)
>
> If the query processor chokes, you could insert ids into temp tables
> as you handle the tables, one by one. (Or two by two or whatever.)
>
> > the problem with that is the query is getting slower & slower when user
> > specify more search criteria,
>
> If more criterias means more data to search and return that may be
> inevitable.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]