Click here to Skip to main content
15,946,683 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am Using free text search in sql server
my sp is like
SQL
Create procedure [dbo].[spExactSearch]
(
  @Keyword nvarchar(255)
)
AS
 BEGIN
 SELECT
       TenderID
      ,TenderTitle
      ,ProductCatID
      ,P.ValueTypeName as ProductCatName
      ,TenderValue
      ,Location
      ,LastDateForSubmission
FROM TenderDetail TD(NOLOCK)
INNER JOIN
   ValueType P(NOLOCK)
   ON
   TD.ProductCatID=P.ValueTypeID
WHERE CONTAINS((ProductSubCatName), @Keyword)
Order By TD.CreatedDate desc
END

the problem is this is working fine for 1 word but when i am using more than 1 word
then it throws an error and i don't want to use Free Text
can someone suggest something like whatever be the value comes in @keyword free text count it as a single word like if i am Using "Stored Procedure" in place of @keyword then its working fine
thanks for help.....
Posted
Comments
Anurag Sinha V 3-Oct-13 2:32am    
Are you trying to compare one of the columns of the table to the @keyword variable?
Anurag Sinha V 3-Oct-13 2:33am    
If yes, then why don't you go for something like "columnname IN ('+@keyword+')'"??
Vishal Pand3y 3-Oct-13 2:47am    
yes i am using one column to compare
bt what i need is whatever is the content of @keyword it should be in single string like "@keyword"
thatraja 3-Oct-13 3:10am    
Any example inputs & outputs?
Vishal Pand3y 3-Oct-13 3:15am    
like with contain i have to use WHERE CONTAINS((ColumnName), '"First Second"')
so how i can convert@Keyword in that Format

1 solution

Hope helpful,
SQL
--You can store each word with single quote separated by commas into @Keyword variable
--Pass string with single quote ' seperated by , comm

Declare @Keyword varchar(200) = '''First'',''third'',''fifth'''
Declare @Query varchar(500)

set @Query = 'select * from TenderDetail where ProductSubCatName in (' + @Keyword + ')'

exec (@Query)
 
Share this answer
 
Comments
Vishal Pand3y 6-Oct-13 3:38am    
thanks padam...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900