SQL Server 2008 FullText Search Query

I have added Full Text Search to my sql server 2008 express database and created an index catalog for two columns in a single table. So now, I have to rewrite one of my stored procedures but I have no idea where to begin. The following is my current SP that I need to convert to take advantage of the full text search capability:

ALTER PROCEDURE [dbo].[sp_page_GetPostsBySearchFront]
(
 @Title nvarchar(256), 
 @Content nvarchar(MAX), 
 @startRowIndex INT,
 @maximumRows INT
) 
AS 
BEGIN
SELECT 
  RowNumber, 
  postId, 
  Title, 
  Content, 
  DateCreated, 
  IsPublished, 
  PublishOnDate, 
  Type, 
  MenuName 
FROM 
(
SELECT ROW_NUMBER() OVER (ORDER BY posts.postId DESC) AS R开发者_StackOverflow中文版owNumber, 
  posts.postId, 
  posts.Title, 
  posts.Content, 
  posts.DateCreated, 
  posts.IsPublished, 
  posts.PublishOnDate, 
  posts.Type, 
  posts.MenuName 
FROM posts 
GROUP BY 
  posts.postId, 
  posts.Title, 
  posts.Content, 
  posts.DateCreated, 
  posts.IsPublished, 
  posts.PublishOnDate, 
  posts.Type, 
  posts.MenuName 
HAVING (posts.Title LIKE N'%' + @Title + N'%') 
OR (posts.Content LIKE N'%' + @Content + N'%') 
AND (posts.IsPublished = 1) 
AND (posts.PublishOnDate <= GETDATE()) 
) as u
WHERE u.RowNumber > @startRowIndex 
AND u.RowNumber <= (@startRowIndex + @maximumRows) 

END

Could some one explain how I go about accomplishing this task? Do I use CONTAINS or FREETEXT and where do I add it. I'm just lost on this? Thank you!

---

Use:

WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY p.postId DESC) AS RowNumber, 
           p.postId, 
           p.Title, 
           p.Content, 
           p.DateCreated, 
           p.IsPublished, 
           p.PublishOnDate, 
           p.Type, 
           p.MenuName 
      FROM POSTS p
     WHERE (   CONTAINS(p.title, @Title)
            OR CONTAINS(p.content, @Content))
       AND p.IsPublished = 1
       AND p.PublishOnDate <= GETDATE() )
 SELECT u.RowNumber, 
        u.postId, 
        u.Title, 
        u.Content, 
        u.DateCreated, 
        u.IsPublished, 
        u.PublishOnDate, 
        u.Type, 
        u.MenuName 
   FROM cte as u
  WHERE u.RowNumber > @startRowIndex 
    AND u.RowNumber <= (@startRowIndex + @maximumRows)

Too bad you have different parameters for searching the title and content fields - could've been consolidated into a single CONTAINS if the parameters are the same value.

The documentation provides a nice breakdown of when they suggest Full Text Search (FTS) functionality. The majority of examples suggest CONTAINS/CONTAINSTABLE, and use FREETEXT/FREETEXTTABLE for:

• The inflectional forms are the different tenses of a verb or the singular and plural forms of a noun.
• A thesaurus defines user-specified synonyms for terms.

---

OK, Here is what I did to make it work. The reason why I was receiving the noise word message is because I did not set the parameter value length @search correctly. Once I realized that and changed it, it works fine.

ALTER PROCEDURE [dbo].[sp_page_GetPostsByFTS] 
( 
    @search nvarchar(255), 
    @startRowIndex INT, 
    @maximumRows INT 
) 
AS 
BEGIN 
SELECT 
    RowNumber, 
    postId, 
    Title, 
    Content, 
    DateCreated, 
    IsPublished, 
    PublishOnDate, 
    Type, 
    MenuName 
FROM 
( 
    SELECT 
    ROW_NUMBER() OVER (ORDER BY posts.postId DESC) AS RowNumber, 
        posts.postId, 
        posts.Title, 
        posts.Content, 
        posts.DateCreated, 
        posts.IsPublished, 
        posts.PublishOnDate, 
        posts.Type, 
        posts.MenuName 
    FROM posts 
    WHERE FREETEXT((Title,Content),@search) 
    AND (posts.IsPublished = 1) 
    AND (posts.PublishOnDate <= GETDATE()) 
) 
as u 
WHERE u.RowNumber > @startRowIndex 
AND u.RowNumber <= (@startRowIndex + @maximumRows) 
END