USP_DATALIST_GETPAGES

CREATE procedure dbo.USP_DATALIST_GETPAGES
(
    @DATALISTID uniqueidentifier,
    @APPUSERID uniqueidentifier
)
as
begin
    set nocount on;

    declare @IsSysAdmin bit;

    select @IsSysAdmin = ISSYSADMIN 
        from dbo.APPUSER 
        where ID = @APPUSERID;

    declare @RecordTypeID uniqueidentifier;
    select @RecordTypeID = RECORDTYPEID from dbo.DATALISTCATALOG where ID = @DATALISTID;

    with cte_pages (ID, PAGEDEFINITIONSPEC, EXPRESSIONDATAFORMID) as
    (select p.ID, p.PAGEDEFINITIONSPEC,
        p.[PAGEDEFINITIONSPEC].value('declare namespace bbspec="bb_appfx_pagedefinition";/bbspec:PageDefinitionSpec[1]/@ExpressionDataFormID','nvarchar(36)') AS [EXPRESSIONDATAFORMID]
        from dbo.pagedefinitioncatalog p
        where (@RecordTypeID is null or @RECORDTYPEID = p.CONTEXTRECORDTYPEID)
            and exists ((select T.c.value('@ID', 'uniqueidentifier')
                        from PAGEDEFINITIONSPEC.nodes('declare namespace ns="bb_appfx_pagedefinition"; //ns:DataList') as T(c)
                        where T.c.value('@ID', 'uniqueidentifier') = @DATALISTID))
    )
    select ID, PAGEDEFINITIONSPEC
    from cte_pages
    where (EXPRESSIONDATAFORMID = '00000000-0000-0000-0000-000000000000') 
        or (@IsSysAdmin = 1)
        or ((select top 1 GRANTORDENY
                    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM
                    where APPUSERID = @APPUSERID
                    and DATAFORMINSTANCECATALOGID=EXPRESSIONDATAFORMID
                    order by GRANTORDENY ASC) = 1);

    return 0;
end