03.01.00.SqlDataProvider

IF OBJECT_ID(N'{databaseOwner}[{objectQualifier}EngageF3_spSearchTextHtmlContent]', N'P') IS NOT NULL
    DROP PROC {databaseOwner}[{objectQualifier}EngageF3_spSearchTextHtmlContent]
GO

CREATE PROC {databaseOwner}[{objectQualifier}EngageF3_spSearchTextHtmlContent]
    @searchValue nvarchar(4000),
    @portalId int,
    @lowerTabId int,
    @upperTabId int
AS
 
    DECLARE @PublishedContent TABLE (ItemId int)
    INSERT INTO @PublishedContent (ItemId)
    SELECT ht.ItemId
    FROM {databaseOwner}[{objectQualifier}HtmlText] ht
    WHERE ht.IsPublished = 1 
      AND ht.LastModifiedOnDate = (SELECT MAX(LastModifiedOnDate) FROM {databaseOwner}[{objectQualifier}HtmlText] WHERE ModuleID = ht.ModuleID AND IsPublished = 1)
 
    SELECT ht.ModuleID, ht.ItemID, m.TabID, ht.Content, ht.StateID, m.PortalID, m.ModuleTitle, t.TabName, p.PortalName
    FROM {databaseOwner}[{objectQualifier}HtmlText] ht 
     JOIN @PublishedContent pc ON (ht.ItemID = pc.ItemId)
     JOIN {databaseOwner}[{objectQualifier}vw_Modules] m ON (m.ModuleID = ht.ModuleID)
     JOIN {databaseOwner}[{objectQualifier}vw_Tabs] t ON (t.TabID = m.TabID)
     JOIN {databaseOwner}[{objectQualifier}vw_Portals] p ON (p.PortalID = t.PortalID)
    WHERE ht.Content COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%' + REPLACE(REPLACE(@searchValue, '\', '\\'), '%', '\%') + '%' ESCAPE '\'
      AND (@portalId IS NULL OR m.PortalID = @portalId)
      AND (@lowerTabId IS NULL OR t.TabID >= @lowerTabId)
      AND (@upperTabId IS NULL OR t.TabID <= @upperTabId)
GO

IF OBJECT_ID(N'{databaseOwner}[{objectQualifier}EngageF3_spSearchPublishContent]', N'P') IS NOT NULL
    DROP PROC {databaseOwner}[{objectQualifier}EngageF3_spSearchPublishContent]
GO

CREATE PROC {databaseOwner}[{objectQualifier}EngageF3_spSearchPublishContent]
    @searchValue nvarchar(4000),
    @portalId int
AS
 
 EXEC sp_executesql N'
     SELECT a.Name, a.ItemId, a.ArticleText, t.TabID, t.TabName, p.PortalName
     FROM {databaseOwner}[{objectQualifier}Publish_vwArticles] a
      JOIN {databaseOwner}[{objectQualifier}vw_Tabs] t ON (t.TabID = a.DisplayTabId)
      JOIN {databaseOwner}[{objectQualifier}vw_Portals] p ON (p.PortalID = t.PortalID)
     WHERE a.ArticleText COLLATE SQL_Latin1_General_CP1_CS_AS LIKE ''%'' + REPLACE(REPLACE(@searchValue, ''\'', ''\\''), ''%'', ''\%'') + ''%'' ESCAPE ''\''
       AND (@portalId IS NULL OR a.PortalID = @portalId)
       AND a.IsCurrentVersion = 1',
   N'@searchValue nvarchar(4000), @portalId int',
   @searchValue, @portalId
GO