spSearchByText

CREATE PROCEDURE [dbo].[spSearchByText]
                            (
                                @ClientsID int,
                                @ClientUsersID int,
                                @SearchText nvarchar(768),
                                @SearchTextRaw nvarchar(255),
                                @FirstWord nvarchar(200),
                                @SearchTypeFilter int,
                                @SiteContentFilter int,
                                @PageContentFilter int,
                                @ApplyDateFilter bit,
                                @StartDateFilter datetime,
                                @EndDateFilter datetime,
                                @TagOnlySearch bit,
                                @ClientSitesIDs XML
                            )
                            AS
                            BEGIN

                                DECLARE @CurrentDate datetime
                                DECLARE @ContentPages table(
                                PageID int,
                                PageName nvarchar(255),
                                PageContentID int,
                                SiteContentID int,
                                PageGUID uniqueidentifier,
                                TemplateGUID uniqueidentifier null,
                                ContentTypesID int)
                                DECLARE @ValidSites table(SiteID int)

                                SET @CurrentDate = getutcdate()

                                INSERT INTO @ValidSites
                                SELECT ID
                                FROM ClientSites CS
                                WHERE CS.Deleted = 0 and dbo.UFN_CLIENTSITE_ISPUBLISHED(CS.ID) = 1

                                --get all pages with matching targeted content
                                INSERT INTO @ContentPages select DISTINCT sp.ID, CASE LEN(isnull(sp.DisplayName, '')) WHEN 0 THEN sp.PageName ELSE sp.DisplayName END AS PageName,
                                pc.ID, s.SiteContentID, sp.guid, (SELECT sp2.GUID from SitePages sp2 where sp2.ID = sp.TemplatePageID), SC.ContentTypesID
                                FROM Search s
                                INNER JOIN TargetedContentChecks tcc on tcc.ContentID = s.SiteContentID
                                INNER JOIN TargetedContentPart tp on tp.ID = tcc.TargetedPartID
                                INNER JOIN PageContent pc on pc.SiteContentID = tp.SiteContentID
                                INNER JOIN SitePages sp on sp.IsTemplatePage = 0 and sp.id = pc.SitePagesID
                                INNER JOIN dbo.SiteContent SC on SC.ID = S.SiteContentID
                                where sp.deleted = 0 and SC.deleted = 0

                                --get all pages with matching content
                                INSERT INTO @ContentPages select DISTINCT sp.ID, CASE LEN(isnull(sp.DisplayName, '')) WHEN 0 THEN sp.PageName ELSE sp.DisplayName END AS PageName,
                                pc.ID, s.SiteContentID, sp.guid, (SELECT sp2.GUID from SitePages sp2 where sp2.ID = sp.TemplatePageID), SC.ContentTypesID
                                FROM SitePages sp
                                INNER JOIN PageContent pc on sp.IsTemplatePage = 0 and pc.SitePagesID = sp.id
                                INNER JOIN Search s on s.SiteContentID = pc.SiteContentID
                                INNER JOIN dbo.SiteContent SC on SC.ID = S.SiteContentID
                                where sp.deleted = 0 and SC.deleted = 0

                                --delete pages from locked artifacts
                                delete @ContentPages
                                from @ContentPages CP
                                inner join dbo.pagemodelitem PMI on CP.PageGUID = PMI.OBJECTGUID
                                inner join dbo.pagemodel PM on PM.ID = PMI.PageModelID
                                where PM.LOCKARTIFACTS = 1

                                --delete pages from sites outside the scope of this search
                                if @ClientSitesIDs.exist('/ids/id') = 1
                                    delete @ContentPages
                                    from @ContentPages CP
                                    inner join dbo.SitePages SP on SP.ID = CP.PageID
                                    left outer join @ClientSitesIDs.nodes('/ids/id') IDS(ID) on IDS.ID.value('@value', 'int') = SP.ClientSitesID
                                    where IDS.ID.value('@value', 'int') is null

                                --delete pages from unpublished or deleted sites
                                delete @ContentPages
                                from @ContentPages CP
                                inner join dbo.SitePages SP on SP.ID = CP.PageID
                                left outer join @ValidSites VS on VS.SiteID = SP.ClientSitesID
                                where VS.SiteID Is null

                                insert into [dbo].[SearchLog]
                                (SearchTypeFilter, ClientID, ClientUserID, [Text])
                                values
                                (@SearchTypeFilter, @ClientsID, @ClientUsersID, @SearchTextRaw)

                                if @SearchTypeFilter <= 0
                                    SET @SearchTypeFilter = NULL

                                if @SiteContentFilter <= 0
                                    SET @SiteContentFilter = NULL

                                -- Tag search first
                                if @ApplyDateFilter = 1
                                    if @TagOnlySearch = 1
                                        SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
                                        FROM Search s
                                        INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
                                        and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
                                        and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
                                        INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
                                        INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                                        where [dbo].[fnSearchDateRangeIsValid](@StartDateFilter, @EndDateFilter, s.SearchDate1, s.SearchDate2) = 1
                                        ORDER BY k.RANK desc
                                    else
                                        SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
                                        FROM Search s
                                        INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
                                        and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
                                        and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
                                        INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
                                        INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                                        where [dbo].[fnSearchDateRangeIsValid](@StartDateFilter, @EndDateFilter, s.SearchDate1, s.SearchDate2) = 1
                                        UNION ALL
                                        SELECT DISTINCT cp.PageID ID , cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 0 AS ResultFromtag, cp.ContentTypesID
                                        FROM Search s
                                        INNER JOIN CONTAINSTABLE(Search, SearchText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
                                        and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
                                        and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
                                        INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
                                        INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                                        where [dbo].[fnSearchDateRangeIsValid](@StartDateFilter, @EndDateFilter, s.SearchDate1, s.SearchDate2) = 1
                                        ORDER BY k.RANK desc
                                else
                                    if @TagOnlySearch = 1
                                        SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
                                        FROM Search s
                                        INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
                                        and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
                                        and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
                                        INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
                                        INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                                        ORDER BY k.RANK desc
                                    else
                                        SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
                                        FROM Search s
                                        INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
                                        and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
                                        and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
                                        INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
                                        INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                                        UNION ALL
                                        SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 0 AS ResultFromtag, cp.ContentTypesID
                                        FROM Search s
                                        INNER JOIN CONTAINSTABLE(Search, SearchText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
                                        and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
                                        and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
                                        INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
                                        INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                                        ORDER BY k.RANK desc
                                END