USP_QUERYVIEWCATALOG_GETROOTLIST

CREATE procedure dbo.USP_QUERYVIEWCATALOG_GETROOTLIST
(
    @APPUSERID uniqueidentifier,
    @USEINREPORTMODELGENERATOR bit = null
)
as 
    set nocount on;

    declare @IS_SYS_ADMIN bit;
    declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;
    declare @OKVIEWS table (QVID uniqueidentifier);

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

    if @IS_SYS_ADMIN = 0
        select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@APPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f')

    if (@IS_SYS_ADMIN <> 1 and @HASQUERYVIEWSYSTEMPRIVILEGE <> 1)
        insert into @OKVIEWS(QVID)
            select QUERYVIEWCATALOGID from dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@APPUSERID);

    select 
        QUERYVIEWCATALOG.ID, 
        QUERYVIEWCATALOG.OBJECTNAME, 
        QUERYVIEWCATALOG.DISPLAYNAME, 
        QUERYVIEWCATALOG.RECORDTYPEID,
        RECORDTYPE.NAME as 'RECORDTYPE', 
        QUERYVIEWCATALOG.OBJECTTYPE,
        QUERYVIEWCATALOG.DESCRIPTION,
        QUERYVIEWCATALOG.USEINREPORTMODELGENERATOR
    from 
        dbo.QUERYVIEWCATALOG 
    left outer join 
        dbo.RECORDTYPE on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
    where 
        QUERYVIEWCATALOG.ROOTOBJECT = 1 and ((QUERYVIEWCATALOG.OBJECTTYPE = 0) or (QUERYVIEWCATALOG.OBJECTTYPE = 1 and (QUERYVIEWCATALOG.PARAMETERMETADATAXML is null)))
        and (@USEINREPORTMODELGENERATOR is null or QUERYVIEWCATALOG.USEINREPORTMODELGENERATOR = @USEINREPORTMODELGENERATOR)
        and (
                (@IS_SYS_ADMIN =1 or @HASQUERYVIEWSYSTEMPRIVILEGE = 1)  
                    or 
                (QUERYVIEWCATALOG.ID in (select QVID from @OKVIEWS))
            )
    order by RECORDTYPE.NAME, QUERYVIEWCATALOG.DISPLAYNAME;

return 0;