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
Create procedure [dbo].[spExactSearch]
  @Keyword nvarchar(255)
      ,P.ValueTypeName as ProductCatName
FROM TenderDetail TD(NOLOCK)
   ValueType P(NOLOCK)
WHERE CONTAINS((ProductSubCatName), @Keyword)
Order By TD.CreatedDate desc

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.....
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,
--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
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