USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_REPORTS_2

CREATE procedure dbo.USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_REPORTS_2
(
  @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 
    REP.ID,
    REP.UINAME as NAME,
    replace(cast(coalesce(REP.REPORTSPECXML.value('declare namespace bbfa="bb_appfx_report";/bbfa:ReportSpec[1]/bbfa:Folder[1]','nvarchar(100)'), 'TEST') as nvarchar(255)), '/','\') as SECURITYUIFOLDER,
    REP.DESCRIPTION,
    PERM.ID AS PERMISSIONID,
    PERM.GRANTORDENY
  from
    dbo.V_INSTALLED_REPORTCATALOG as REP
  left join
    dbo.SYSTEMROLEPERM_REPORT as PERM on PERM.REPORTCATALOGID = REP.ID and PERM.SYSTEMROLEID = @SYSTEMROLEID
  where  
    (@ONLYASSIGNED = 0 or PERM.ID is not null)
  order by 
    SECURITYUIFOLDER, REP.UINAME;