[dba-SQLServer] SQL WildCard Searches

Mark,

A stored procedure for each field you want to search.

Something similar to this:

CREATE PROCEDURE uspSearchField1
     @Word varchar(20)
AS
SELECT *
FROM MyTable
WHERE TheField LIKE '%' + @word + '%'

You would execute the SP from Access with a pass-through query.

EXEC USPSearchField1 'Looking'

You could store the words in a table and use 2 queries, one without 
the parameter and the second one that you change the SQL statement 
each time using the SQL from the first plus the word from the 
table.  Then you could loop through the table and through all of the 
queries and get all of the matches rather quickly.

Robert

At 12:00 PM 7/24/2006, you wrote:
>Date: Mon, 24 Jul 2006 16:34:27 +0000
>From: "Mark A Matte" <markamatte at hotmail.com>
>Subject: Re: [dba-SQLServer] SQL WildCard Searches
>To: dba-sqlserver at databaseadvisors.com
>Message-ID: <BAY121-F18B18BCDBEE8D108735447D2650 at phx.gbl>
>Content-Type: text/plain; format=flowed
>
>Hello All,
>
>I am finally getting my data from Informix into SQL Server.  I need to do
>wild card searches(about 5-7 different key word combinations) on 10
>different fields.  In Access I planned on having a criteria table, each
>record being the end of the needed sql statement...and using VBA to loop
>through these criteria records and executing them against each of the 10
>fields.
>
>Any suggestions as to my best approach in SQL server?
>
>Also,  I have to do this 5 more times...same fields...but different set of
>key words each time, so I was kinda looking for some automation...or at
>least the easiest way to modify for each new request.
>
>Any feedback is appreciated.
>
>Thanks,
>
>Mark A. Matte