To use full-text search, you need to
SET full-text index on the table first,
You use the CREATE FULLTEXT INDEX command to create full-text indexes
UseAdventurework2008;
CREATE
FULLTEXT
INDEX ON Person.Contact(Firstname)
KEY INDEX pk_Contact_ContactID
In this example,
Person.Contact is the name of the table you are full-text indexing, and
pk_Contact_ContactID
is the Full-Text Search key. A Full-Text Search key must be a unique, non-nullable, single-column index that is not offline and has a maximum size of 900 bytes. Note that this full-text
index is created on the default full-text catalog. Once again, if you do not have a default full-text catalog, you get the following message:
A default full-text catalog does not exist in database ‘AdventureWorks’ or user does not have permission to perform this action.
You specify a full-text catalog by using the following command:
UseAdventureWorks;
CREATE
FULLTEXT
INDEX ON Person.Contact
(Firstname, Lastname)
KEY
INDEX pk_Contact_ContactID
ON MyCatalog
After creating the full-text index and
full-text catalog as shown above you can create the stored procedure like this:
create
Procedure [dbo].[usp_Search_Contact]
(
@FirstNameSearch
varchar(50)
)
AS
set@FirstNameSearch
=
'"*' + @FirstNameSearch
+
'*"'
SELECTFirstName
FROMPerson.Contact
WHERE
CONTAINS(FirstName, @FirstNameSearch);
I hope this helps a bit
