Passing the database columns as parameter in Contains or Freetext

Question

I am trying to pass the searchable columns as parameter to the stored procedure, but it never work

contains(@RTitleColumn, @searchTerm)

I am passing the @RtitleColumn as the first CONTAINS parameter

here is the full query

SELECT  Lid As ItemID, Lhits As Hits, LImageID As ImageID, LNAME As Title
            , LCategory AS CategoryID, Lactive AS isActive,LRate AS SumRate,LRAteNo AS CommentCount,
            ROW_NUMBER() OVER (ORDER BY LID desc) AS RowRank
            FROM    dbo.tblRecipes
            where
            contains(@RTitleColumn, @searchTerm) and (@Activeflag is null or LActive=@Activeflag)

@GilPeretz: I'm pretty sure the OP is taking about the `CONTAINS` SQL Server Fulltext Search command here... — marc_s, Feb 02 '13 at 18:08
Hi, Yes, what i am trying to to create dynamic sql and pass the contains search columns field as a variable, it doesn't accept having variable, it only accepts having the * or the actual full text column name for example "LName" — Moe9977, Feb 03 '13 at 19:17
you will need to use dynamic sql... store the query into a variable, and append the RTitleColumn value inside the contains predicate. then pass in the searchterm as a parameter into the sp_executesql command — elvis, Feb 07 '13 at 23:45

score 0 · Answer 1 · edited Feb 29 '16 at 05:59

(Assuming parameter @searchTerm is declared as nvarchar(50)

Declare @MainSQL nvarchar(200)



set @MainSQL =  


    N'SELECT Lid As ItemID, Lhits As Hits,LImageID As ImageID, 
       LNAME As Title, LCategory AS CategoryID, Lactive AS isActive, 
       LRate AS SumRate,LRAteNo AS CommentCount, 
       ROW_NUMBER() OVER (ORDER BY LID desc) AS RowRank 
       FROM    dbo.tblRecipes 
       where                                  
       contains(' + @RTitleColumn + ', @searchTerm)'

Execute sp_executesql, @MainSQL, N'@searchTerm nvarchar(50)',@searchTerm = @searchTerm

Passing the database columns as parameter in Contains or Freetext

1 Answers1