Is it possible to write a Stored Procedure that takes a string of
search keywords as argument and returns the recordset? At the mo I am
passing the WHERE String as argument.
I got this technique from an Extreme Ultradev tutorial by Rick Curtis
it looked quite ok:
http://www.princeton.edu/~rcurtis/ul...utorial12.html
I have to admit, one of the main reason for passing the WHERE string is
that I do not know how to do the string splitting / parsing and putting
together in a Stored Procedure. I bet T-SQL would be just as powerful
as VBScript if I just knew it well enough.
What I liked about having built them on the web script was the
flexibility allowing to potentially build an advanced search without
having to change the stored procedure - but this is not crucial I could
always write several stored procedures or add parameters to the SP.
Here is what I have achieved in this way:
User can enter one ore more keywords separated by space.
Search algorithm returns results across a number of fields where ALL
search words are contained in any of these.
Search results will always be formatted a certain way and displayed in
a html table no matter how the search procedure / criteria is varied.
Here is the algorithm (that now works in ASP)
1. split search string into separate keywords
2. build where condition based on single keyword, concatenating all
searched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")
3. concatenate all these where conditions and pass to stored procedure.
4. stored procedure takes care of all other logic (e.g. Joins, which
fields are searched etc.). It uses a string variable @SQL to build the
complete search string and then does
execute (@SQL);
to create the recordset.
I bet there is a way to move 1. 2. and 3. into the SP (and I would feel
better if it was) but I don't have the expertise to do this. If anybody
wants to help me this is very welcome.
I can also post my original code to clarify, just want to avoid too
long posts.
Cheers
Axel