EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[WPPAG_PAGE]', @action=N'create', @keyname=N'PK_WPPAG_PAGE', @ftcat=N'WFPortalSearch'
GO
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[WPPAG_PAGE]', @colname=N'PAG_C_KEYWORDS', @action=N'add'
GO
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[WPPAG_PAGE]', @colname=N'PAG_C_METATAGS', @action=N'add'
GO
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[WPPAG_PAGE]', @colname=N'PAG_C_TITLE', @action=N'add'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[WPPAG_PAGE]', @action=N'start_change_tracking'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[WPPAG_PAGE]', @action=N'start_background_updateindex'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[WPPCT_PUBLISHED_CONTENT]', @action=N'create', @keyname=N'PK_WPACT_APPROVED_CONTENT', @ftcat=N'WFPortalSearch'
GO
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[WPPCT_PUBLISHED_CONTENT]', @colname=N'PCT_C_CONTENT', @action=N'add'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[WPPCT_PUBLISHED_CONTENT]', @action=N'start_change_tracking'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[WPPCT_PUBLISHED_CONTENT]', @action=N'start_background_updateindex'
GO

CREATE PROCEDURE [dbo].[WPSearch]
      @SearchPattern varchar(150),
      @AreaId int = 0
AS
BEGIN
      SET NOCOUNT ON;

      IF @AreaId = 0
      BEGIN
            SELECT DISTINCT   ((ISNULL(CTPAGTITLE.[RANK], 0) * 5) + (ISNULL(CTPAGKEYWORDS.[RANK], 0) * 4) + (ISNULL(CTPAGMETATAGS.[RANK], 0) * 2) + (ISNULL(CTPCT.[RANK], 0))) AS RANKING, 
                        PAG_C_TITLE, PAG_C_KEYWORDS,
                        PAG_C_METATAGS, PAG_C_READERS, 
                        PAG_C_ALIAS, PAG_N_ID
            FROM WPPAG_PAGE AS PAG
            LEFT JOIN WPPCT_PUBLISHED_CONTENT AS PCT ON PCT.PCT_N_PAG_N_ID = PAG.PAG_N_ID
            LEFT JOIN FREETEXTTABLE(WPPCT_PUBLISHED_CONTENT, PCT_C_CONTENT, @SearchPattern) AS CTPCT
            ON PCT.PCT_N_CTT_N_ID = CTPCT.[KEY]
            LEFT JOIN FREETEXTTABLE(WPPAG_PAGE, PAG_C_TITLE, @SearchPattern) AS CTPAGTITLE
            ON PAG.PAG_N_ID = CTPAGTITLE.[KEY]
            LEFT JOIN FREETEXTTABLE(WPPAG_PAGE, PAG_C_KEYWORDS, @SearchPattern) AS CTPAGKEYWORDS
            ON PAG.PAG_N_ID = CTPAGKEYWORDS.[KEY]
            LEFT JOIN FREETEXTTABLE(WPPAG_PAGE, PAG_C_KEYWORDS, @SearchPattern) AS CTPAGMETATAGS
            ON PAG.PAG_N_ID = CTPAGMETATAGS.[KEY]
            WHERE (CTPAGTITLE.[RANK] IS NOT NULL OR 
                        CTPAGKEYWORDS.[RANK] IS NOT NULL OR
                        CTPAGMETATAGS.[RANK] IS NOT NULL OR
                        CTPCT.[RANK] IS NOT NULL)
            ORDER BY RANKING DESC
      END
      ELSE
      BEGIN
            SELECT DISTINCT   ((ISNULL(CTPAGTITLE.[RANK], 0) * 5) + (ISNULL(CTPAGKEYWORDS.[RANK], 0) * 4) + (ISNULL(CTPAGMETATAGS.[RANK], 0) * 2) + (ISNULL(CTPCT.[RANK], 0))) AS RANKING, 
                        PAG_C_TITLE, PAG_C_KEYWORDS,
                        PAG_C_METATAGS, PAG_C_READERS, 
                        PAG_C_ALIAS, PAG_N_ID
            FROM WPPAG_PAGE AS PAG
            LEFT JOIN WPPCT_PUBLISHED_CONTENT AS PCT ON PCT.PCT_N_PAG_N_ID = PAG.PAG_N_ID
            LEFT JOIN FREETEXTTABLE(WPPCT_PUBLISHED_CONTENT, PCT_C_CONTENT, @SearchPattern) AS CTPCT
            ON PCT.PCT_N_CTT_N_ID = CTPCT.[KEY]
            LEFT JOIN FREETEXTTABLE(WPPAG_PAGE, PAG_C_TITLE, @SearchPattern) AS CTPAGTITLE
            ON PAG.PAG_N_ID = CTPAGTITLE.[KEY]
            LEFT JOIN FREETEXTTABLE(WPPAG_PAGE, PAG_C_KEYWORDS, @SearchPattern) AS CTPAGKEYWORDS
            ON PAG.PAG_N_ID = CTPAGKEYWORDS.[KEY]
            LEFT JOIN FREETEXTTABLE(WPPAG_PAGE, PAG_C_KEYWORDS, @SearchPattern) AS CTPAGMETATAGS
            ON PAG.PAG_N_ID = CTPAGMETATAGS.[KEY]
            WHERE PAG_N_ARE_N_ID = @AreaId AND
                        (CTPAGTITLE.[RANK] IS NOT NULL OR 
                        CTPAGKEYWORDS.[RANK] IS NOT NULL OR
                        CTPAGMETATAGS.[RANK] IS NOT NULL OR
                        CTPCT.[RANK] IS NOT NULL)
            ORDER BY RANKING DESC
      END
END
GO
