USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_REPORTS

                CREATE procedure [dbo].[USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_REPORTS]
                (
                    @SYSTEMROLEID uniqueidentifier,
                    @ONLYASSIGNED bit = 0
                )
                /* Used by the SecurityGetRoleFeaturePermTreeRequest web service */
                as
                set nocount on;

                if @SYSTEMROLEID is null
                begin
                    raiserror('@SYSTEMROLEID is required',16,10);
                    return 9;
                end;

                select 
                    CAT.ID,
                    CAT.NAME AS DISPLAYNAME, 
                    replace(cast(coalesce(CAT.REPORTSPECXML.value('declare namespace bbfa="bb_appfx_report";/bbfa:ReportSpec[1]/bbfa:Folder[1]','nvarchar(100)'), 'TEST') as nvarchar(255)), '/','\') as SECURITYUIFOLDER,
                    CAT.DESCRIPTION,
                    PERM.GRANTORDENY, 
                    PERM.ID AS PERMISSIONID,
                    cast(1 as bit) as DISPLAYFEATURE,
                    CAT.REPORTSPECXML as SPECXML
                from 
                    dbo.REPORTCATALOG as CAT
                left outer join 
                    (
                    select 
                        ID,
                        GRANTORDENY,
                        REPORTCATALOGID 
                    from  
                        dbo.SYSTEMROLEPERM_REPORT as PERM
                    where 
                        SYSTEMROLEID=@SYSTEMROLEID
                    )
                    as PERM on CAT.ID=PERM.REPORTCATALOGID
                where  
                    ((PERM.ID is not null) or (@ONLYASSIGNED=0))
                order by 
                    SECURITYUIFOLDER, CAT.NAME;