how can i write the procedure for search using logical operator?

Question

hi, in my application i have to write the procedure for search. so that user can give any combinatin of words like 'ar rehman and slumdog or daneboel ' like this how can i write the procedure for thi. using logical operators.

Answer 1

I think a mixture of Dynamic SQL and a temp table might be the solution. (or one of them)

If you can identify in your application the ANDs and ORs then you could could group the list of ANDs together and place it in a temp table. You could then use that temp table in the IN part of the where clause.

e.g. WHERE data IN (SELECT words FROM #temptable)

For the ORs part i would expect you would have to use dynamic SQL to add those to the end of you WHERE clause, after you have built the IN part with the temp table.

With this sort of thing I would recommend having limits on how many words they could attach for the OR operator otherwise it could get messy making it very hard to maintain.

Answer 2

In the words of the Office helper thing: "It looks like you are trying to create a search engine!..."

If you are trying to create some sort of search then you may well be better off using SQL Full text search or perhaps Lucene, which has been ported to many languages.

If you do want to do this in SQL then you will need dynamic SQL, and my strong recommendation would be to dynamicly generate the SQL on the client, not inside a stored procedure - and when I say dynamically generate SQL I literally mean generate something like:

WHERE (Name = 'rehman' and SomeField = 'slumdog') or Name = 'daneboel'