• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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

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.
0
niceoneishere
Asked:
niceoneishere
  • 4
  • 4
  • 2
2 Solutions
 
OBonioCommented:
Use double quotes around the 2 words :



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

Open in new window

0
 
niceoneishereAuthor 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
0
 
CB_ThirumalaiCommented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OBonioCommented:
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

0
 
niceoneishereAuthor 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
0
 
CB_ThirumalaiCommented:
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

0
 
niceoneishereAuthor 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.
0
 
OBonioCommented:
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

0
 
OBonioCommented:
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

0
 
niceoneishereAuthor 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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now