USP_SIMPLEDATALIST_BBNCSPECIALPAGE

CREATE procedure dbo.[USP_SIMPLEDATALIST_BBNCSPECIALPAGE]
(
  @PAGETYPE nvarchar(50) = null
)
as
  declare @CLIENTSITEID integer;
  declare @CLIENTSITE nvarchar(255);
  declare @CLIENTSITECOUNT integer = 0;
  declare @INCLUDELOCKEDARTIFACTS bit = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('e5e0494b-ba0f-4e23-b8fb-a59112dbf3c8');

  if len(isnull(@PAGETYPE, '')) = 0 set @PAGETYPE = null;

  declare @RESULT table (
    [ID] integer,
    [PAGENAME] nvarchar(255),
    [PAGETYPE] nvarchar(255),
    [SEQ] integer
  );

  declare @SPECIALPAGES table (
    [ID] integer,
    [PAGENAME] nvarchar(255),
    [PAGETYPE] nvarchar(255),
    [SEQ] integer,
    [CLIENTSITE] nvarchar(255)
  );

  declare CLIENTSITES cursor local fast_forward for
    select [ID], [Name] from dbo.[ClientSites] where [Deleted] = 0;

  open CLIENTSITES;

  fetch next from CLIENTSITES into @CLIENTSITEID, @CLIENTSITE;

  while (@@FETCH_STATUS = 0)
    begin
      set @CLIENTSITECOUNT = @CLIENTSITECOUNT + 1;

      insert into @RESULT exec dbo.[spGetSpecialPages] @CLIENTSITEID, @INCLUDELOCKEDARTIFACTS;

      insert into @SPECIALPAGES select *, @CLIENTSITE from @RESULT;

      delete from @RESULT;

      fetch next from CLIENTSITES into @CLIENTSITEID, @CLIENTSITE;
    end

  close CLIENTSITES;
  deallocate CLIENTSITES;

  select 
    [ID] as [VALUE],
    case when @PAGETYPE is not null then '' else [PAGETYPE] + ': ' end +
    [PAGENAME] +    
    case when @CLIENTSITECOUNT > 1 then ' (' + [CLIENTSITE] + ')' else '' end as [LABEL]
  from @SPECIALPAGES
  where @PAGETYPE is null or [PAGETYPE] = @PAGETYPE
  order by [PAGETYPE] asc, [CLIENTSITE] asc, [PAGENAME] asc;