I've been trying to figure out how to use stored procedures for a search

engine I've written in ASP.  I know you can pass a parameter to SQL such

as



    select * from inventory where description like %@searchWord%



where @searchWord is passed in as a parameter.  This works fine if we only

had one word searches...however in my search engine the user can pass any

number of words (up to a limit of 255 characters) so in my ASP it converts

the words into a search string...e.g., "baseball Mets 1970" would be

converted to "(description like %baseball%) or (description like %Mets%)

or (description like %1970%)"...however, I cannot pass this entire string

as a parameter....how can I create a stored procedure that can take a

variable number of search words and convert them into a search string? 

And for added complexity, I also have functions that allow searches for

category, manufacturer, and any combination of these three (description,

manufacturer, and category) which I've coded into my ASP (and works fine)

but I'd like to be able to put them into stored procedures to allow

scalability.  Any examples or sources I can check?  I haven't found

anything in Beginnning ASP Databases that can help me out with this...



Thanks,



Ben

You might want to try using stored procedures that will take parameters and 

then in your asp scripting, just create as many of those as you need for the 

search string and then call the procedure. To determine the number of 

parameters you need you just separate each word.



You may want to pick up a book on SQL and PL/SQL.



Neil



>From: "Ben Lew" <formula1@o...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Passing multiple 'variables' in a search string to 

>SQL 7 in ASP

>Date: Fri, 20 Oct 2000 17:11:35 +0100

>

>I've been trying to figure out how to use stored procedures for a search

>engine I've written in ASP.  I know you can pass a parameter to SQL such

>as

>

>     select * from inventory where description like %@searchWord%

>

>where @searchWord is passed in as a parameter.  This works fine if we only

>had one word searches...however in my search engine the user can pass any

>number of words (up to a limit of 255 characters) so in my ASP it converts

>the words into a search string...e.g., "baseball Mets 1970" would be

>converted to "(description like %baseball%) or (description like %Mets%)

>or (description like %1970%)"...however, I cannot pass this entire string

>as a parameter....how can I create a stored procedure that can take a

>variable number of search words and convert them into a search string?

>And for added complexity, I also have functions that allow searches for

>category, manufacturer, and any combination of these three (description,

>manufacturer, and category) which I've coded into my ASP (and works fine)

>but I'd like to be able to put them into stored procedures to allow

>scalability.  Any examples or sources I can check?  I haven't found

>anything in Beginnning ASP Databases that can help me out with this...

>

>Thanks,

>

>Ben

>