USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUPNAVIGATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUPNAVIGATION
(
    @DATALOADED bit = 0 output,
  @CURRENTAPPUSERID uniqueidentifier,
  @FAVORITESLIST xml = null output,
  @RECENTLYACCESSEDLIST xml = null output,
  @QUERYVIEWADDATTRIBUTEEXTENSIONPROCESS_PARAMETERSETID uniqueidentifier = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

  declare @SECURITYFEATUREID uniqueidentifier = '2bf48303-8e31-48a4-ad8e-6d2e0ad7aa59';
  declare @SECURITYFEATURETYPE tinyint = 1;

  set @FAVORITESLIST = 
    (
      select top 5
        RG.ID as '@RESEARCHLISTID',
        RG.NAME as '@NAME',
        RG.NUMMEMBERS as '@NUMMEMBERS'
      from 
        dbo.RESEARCHGROUP RG 
      inner join 
        dbo.RESEARCHGROUPFAVORITE RGF on RG.ID = RGF.RESEARCHGROUPID and RGF.APPUSERID = @CURRENTAPPUSERID
      left join 
        dbo.RESEARCHGROUPLASTVISITED RGLV on RG.ID = RGLV.RESEARCHGROUPID and RGLV.APPUSERID = @CURRENTAPPUSERID
      where dbo.UFN_RESEARCHGROUP_USERCANACCESS(rg.ID, @CURRENTAPPUSERID) = 1
        and  dbo.UFN_RESEARCHGROUP_USERHASSITEACCESS(rg.ID, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1
      order by 
        RGLV.DATEACCESSED desc, NAME asc
      for xml path('ITEM'), type, elements, root('FAVORITESLIST'), BINARY BASE64
    )

  set @RECENTLYACCESSEDLIST = 
    (
      select top 5
        RG.ID as '@RESEARCHLISTID',
        RG.NAME as '@NAME',
        RG.NUMMEMBERS as '@NUMMEMBERS'
      from 
        dbo.RESEARCHGROUP RG 
      inner join 
        dbo.RESEARCHGROUPLASTVISITED RGLV on RG.ID = RGLV.RESEARCHGROUPID and RGLV.APPUSERID = @CURRENTAPPUSERID
      where dbo.UFN_RESEARCHGROUP_USERCANACCESS(rg.ID, @CURRENTAPPUSERID) = 1
        and  dbo.UFN_RESEARCHGROUP_USERHASSITEACCESS(rg.ID, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1
      order by 
        RGLV.DATEACCESSED desc, NAME asc
      for xml path('ITEM'), type, elements, root('FAVORITESLIST'), BINARY BASE64
    )

  select @QUERYVIEWADDATTRIBUTEEXTENSIONPROCESS_PARAMETERSETID = ID from dbo.QUERYVIEWADDATTRIBUTEEXTENSIONPROCESS where [NAME] = 'Default Query View Add Attribute Extension Business Process';

    set @DATALOADED = 1;

    return 0;