We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How Can I make this Work so i can search more then one word

Medium Priority
218 Views
Last Modified: 2013-11-07
Hi All,

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

 USE [MySiteDB]
GO
/****** Object:  StoredProcedure [dbo].[fulltextprocedure]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
AS
   -- Insert statements for procedure here
    SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
    FROM dbo.SearchTB
    WHERE CONTAINS((dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Description, dbo.SearchTB.ShortDescription),@searchwords)

return

 

now if i supply a single word to searchwords, it works fine but if i give more then one word like below

SE [MySiteDB]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[fulltextprocedure]
        @searchwords = N'black shoes'

SELECT    'Return Value' = @return_value

GO

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.
Comment
Watch Question

Commented:
Use double quotes around the 2 words :



EXEC    @return_value = [dbo].[fulltextprocedure]
        @searchwords = N'"black shoes"'

Open in new window

Author

Commented:
Hi,
Thanks for replying, but my question how would i program it SP . would i have to say something like
CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
AS
   -- Insert statements for procedure here
    SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
    FROM dbo.SearchTB
    WHERE CONTAINS((dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Description, dbo.SearchTB.ShortDescription), "@searchwords")

return
 
i have put the @searchwords in quotes.
well what i want to do is use this SP and bind it to a GridView so when i search something using a textbox, i can then pass that textbox text to this @searchword.
any help is appreciated
ok, you need the query, here it is
SELECT ItemNumber, Title, Price, Name
   FROM dbo.SearchTB
   WHERE ItemNumber LIKE '%' + @searchwords + '%'
OR Title LIKE  '%' + @searchwords + '%'
OR Description LIKE '%' + @searchwords + '%'
OR ShortDescription LIKE '%' + @searchwords + '%'

Open in new window

Commented:
That isn't using Full Text indexing CB...


  -- Insert statements for procedure here
   SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
   FROM dbo.SearchTB
   WHERE CONTAINS((dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Description, dbo.SearchTB.ShortDescription), '"' + @searchwords + '"')

Open in new window

Author

Commented:
Hi OBonio,
Thats giving me syntax error if i driectly try from the SQL management studio. here is the error
Msg 102, Level 15, State 1, Procedure fulltextprocedure, Line 11
Incorrect syntax near '+'.
 Thanks Sir
Sorry for the ignorance OBonio.

Will this be ok?
DECLARE @searchText NVARCHAR(255);
SET @searchText = '"' + @searchwords + '"';
  -- Insert statements for procedure here
   SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
   FROM dbo.SearchTB
   WHERE CONTAINS((dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Description, dbo.SearchTB.ShortDescription), @searchText)

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hello Sir,
yes that works but now i have one more problem, i can post it in a new post if you guys want me to. here is my new SP

USE [MySiteDB]
GO
/****** Object:  StoredProcedure [dbo].[fulltextprocedure]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Me Create date: 02/10/2009
-- Description: search procedure
-- =============================================
ALTER PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
AS
    DECLARE @searchText NVARCHAR(255);
    SET @searchText = '"' + @searchwords + '"';
   -- Insert statements for procedure here
 SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
    FROM dbo.SearchTB
    WHERE CONTAINS(*, @searchText)
    OR CONTAINS(*, ' FORMSOF (THESAURUS, @searchText) ')
    OR CONTAINS(*, 'NEAR @searchText NEAR')
   
return
when i am using this i am getting an error like this
Syntax error near '@searchText' in the full-text search condition 'NEAR @searchText NEAR'.
and the OR CONTAINS(*, ' FORMSOF (THESAURUS, @searchText) ')  is not even working. what i mean is say if i search for RED, then i think it should even look for words similiar to RED.
is there anyway to make these work, I really appreciate it. You Guys Rock.

Commented:
For some reason it doesn't like string concat, so just do it before the select.

CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
AS
   -- Insert statements for procedure here
	SET @searchwords = N'"' + @searchwords + N'"'
    SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
    FROM dbo.SearchTB
    WHERE CONTAINS((dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Description, dbo.SearchTB.ShortDescription), @searchwords)

Open in new window

Commented:
Something like this?

CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
AS
   -- Insert statements for procedure here
	SET @searchwords = N'"' + @searchwords + N'"'
 
	DECLARE @formsOf NVARCHAR(1024)
	DECLARE @nearSearch NVARCHAR(1024)
	SET @formsOf = N'FORMSOF(THESAURUS,' + @searchwords + N')'
	SET @nearSearch = N'NEAR ' + @searchwords + N' NEAR'
    SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
    FROM dbo.SearchTB
    WHERE CONTAINS(*, @searchwords)
			OR CONTAINS(*, @formsOf)
			OR CONTAINS(*, @nearSearch)
RETURN

Open in new window

Author

Commented:
Hi Sir,
its still throwing the same error, i am wondering if there is a way to split the words and do something like @word1 NEAR @word2. is there anyway split the words  in store procedure sir.
or can you suggest me so advice.
Thanks once again
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.