gistfile1.sql

declare
  @Site varchar(2),
  @BlogEntries tinyint,
  @InclStories tinyint,
  @InclGalleries tinyint,
  @InclCCE tinyint,
  @DatoFromDate varchar(8),
  @DatoToDate varchar(8),
  @CreatedFromDateTime datetime,
  @CreatedToDateTime datetime,
  @ModifiedFromDateTime datetime,
  @ModifiedToDateTime datetime,
  @InternalModifiedFromDateTime datetime,
  @InternalModifiedToDateTime datetime,
  @Categories varchar(max),
  @TaxonomyWords varchar(max),
  @LinkGUID uniqueidentifier,
  @CCEModules varchar(max),
  @CCEModuleTypes varchar(max),
  @CCEClasses varchar(max),
  @CCETypes varchar(max),
  @Theme varchar(255),
  @Keywords varchar(max),
  @SynchStories       varchar(max),
  @SynchGalleries     varchar(max),
  @SynchCCEObjects    varchar(max),
  @MainProfiles       varchar(max),
  @CreatedBy varchar(64),
  @AssetTypes varchar(max),
  @ExternalAsset tinyint,
  @Sites varchar(max),
  @PrioSortProfile int,
  @GUIDs varchar(max)
set @Site                = 'KC'                      -- sitecode
set @BlogEntries         = IsNull(100, 100)  -- filter on creator (0=UGC, 1=UGC+mojos, 2=mojos, 3=mojos+edit users, 4=edit users, 5=UGC+edit users, otherwise=all)
set @InclStories         = IsNull(1, 1)    -- include stories
set @InclGalleries       = IsNull(1, 1)  -- include galleries
set @InclCCE             = IsNull(1, 1)        -- include CCE objects
set @DatoFromDate        = '20140119'              -- start date for dato
set @DatoToDate          = '20140915'                -- end date for dato
set @CreatedFromDateTime = '1899-12-30 00:00:00.000'       -- createdtime filter from datetime
set @CreatedToDateTime   = '3014-09-15 12:28:01.778'         -- createdtime filter to datetime, requires the from datetime
set @ModifiedFromDateTime = '1899-12-30 00:00:00.000'     -- modified filter filter from datetime
set @ModifiedToDateTime   = '3014-09-15 12:28:01.778'       -- modified filter to datetime, requires the from datetime
set @InternalModifiedFromDateTime = '1899-12-30 00:00:00.000'       -- internal modified filter from datetime
set @InternalModifiedToDateTime   = '3014-09-15 12:28:01.778'         -- internal modified filter to datetime, requires the from datetime
set @Categories          = IsNull('%', '%') -- categories filters list, used in "like" comparison
set @TaxonomyWords       = null             -- taxonomywords filter on attached taxwords
set @LinkGUID            = null                  -- links filter
set @CCEModules          = null                -- CCE modules filter
set @CCEModuleTypes      = null            -- CCE module type filter
set @CCEClasses          = null                -- CCE classes filter
set @CCETypes            = null                  -- CCE types filter
set @Theme               = IsNull(null, '')       -- Story theme
set @Keywords            = null                  -- Story keywords
set @SynchStories        = IsNull(null, '')
set @SynchGalleries      = IsNull(null, '')
set @SynchCCEObjects     = IsNull(null, '')
set @MainProfiles        = IsNull(null, '')
set @CreatedBy           = IsNull(null, '')   -- CreatedBy
set @AssetTypes          = IsNull(null, '')  -- AssetTypes
set @ExternalAsset       = IsNull(0, 0)  -- ExternalAsset
set @PrioSortProfile = IsNull(0, '') -- PrioSort Profile
set @Sites               = IsNull(null, '')       -- Sites
set @GUIDs               = IsNull(null, '')       -- GUIDs

declare
  @PageNumber int,
  @PageSize   int,
  @ItemStart  int,
  @ItemEnd    int,
  @TotalCount int

set @PageNumber = 1
set @PageSize   = 50
set @ItemStart  = ((@PageNumber-1) * @PageSize) + 1
set @ItemEnd    = @ItemStart + @PageSize - 1
set @TotalCount = 0

-- Prepare category filter
declare @CatsTable table (
  Site varchar (2),
  Category varchar (30) primary key (Site, Category)
)

insert into @CatsTable
  select distinct Avis, Kategori
  from web.dbo.Kategorier k (nolock)
  inner join web.dbo.fCommaText(@Categories, ',') catFilter
    on (k.Kategori like catFilter.Value)
  where k.Avis = @Site

-- Prepare table for temp results
declare @Table table (
  [ItemTypeID] tinyint,
  [Site] varchar (2),
  ItemGUID uniqueidentifier,
  DesignClass varchar (1),
  Sort0 datetime
)

-- Fill the table with content
-- if (@InclStories = 1)
begin
  select @TotalCount = Count(distinct ah.RowGUID)
  from web.dbo.Artikkler_Hode ah (nolock)
    inner join @CatsTable catFilter on (ah.Avis = catFilter.Site and ah.Kategori = catFilter.Category)
    inner join web.dbo.artikkler_status s (nolock)
      on (s.Avis = ah.Avis and s.Dato = ah.Dato and ah.Kategori = s.Kategori and s.Lopenr = ah.Lopenr)
    left join web.dbo.Kategorier kat (nolock)
      on (kat.Avis = ah.Avis and Kat.Kategori = ah.Kategori)
  where
    ah.Avis = @Site
    and ah.Dato between @DatoFromDate and @DatoToDate
    and (s.[Status] = 9 or s.Status is null)

  insert into @Table
  select distinct top (@ItemEnd)
    0 as [ItemTypeID],
    ah.Avis as [Site],
    ah.RowGUID as ItemGUID,
    ah.OvrSubClass as DesignClass,
    ah.Sist_endret
  from web.dbo.Artikkler_Hode ah (nolock)
    inner join @CatsTable catFilter on (ah.Avis = catFilter.Site and ah.Kategori = catFilter.Category)
    inner join web.dbo.artikkler_status s (nolock)
      on (s.Avis = ah.Avis and s.Dato = ah.Dato and ah.Kategori = s.Kategori and s.Lopenr = ah.Lopenr)
    left join web.dbo.Kategorier kat (nolock)
      on (kat.Avis = ah.Avis and Kat.Kategori = ah.Kategori)
  where
    ah.Avis = @Site
    and ah.Dato between @DatoFromDate and @DatoToDate
    and (s.[Status] = 9 or s.Status is null)
  order by ah.Sist_endret desc, ItemGUID asc
end