How Can I make this Work so i can search more then one word
Posted on 2009-02-11
I have a SP based on a table which enabled with Full Text Search. the sp work great if the searchword is a single word( literally just a single word), as soon as i supply more then one word, it throws an error. the below is my SP code
/****** Object: StoredProcedure [dbo].[fulltextprocedure] ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
-- Insert statements for procedure here
SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
WHERE CONTAINS((dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Description, dbo.SearchTB.ShortDescription),@searchwords)
now if i supply a single word to searchwords, it works fine but if i give more then one word like below
DECLARE @return_value int
EXEC @return_value = [dbo].[fulltextprocedure]
@searchwords = N'black shoes'
SELECT 'Return Value' = @return_value
the error i am getting is
Msg 7630, Level 15, State 3, Procedure fulltextprocedure, Line 9
Syntax error near 'shoes' in the full-text search condition 'black shoes'.
how i can i make it work so i can search for more then one word.
Thank i appreciate it.