USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_SEARCHLISTS_2

CREATE procedure dbo.USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_SEARCHLISTS_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
    SL.ID,
    SL.UINAME as NAME,
    SL.SECURITYUIFOLDER,
    SL.DESCRIPTION,
    PERM.ID as PERMISSIONID,
    PERM.GRANTORDENY
  from 
    dbo.V_INSTALLED_SEARCHLISTCATALOG as SL
  left join
    dbo.SYSTEMROLEPERM_SEARCHLIST PERM on PERM.SEARCHLISTCATALOGID = SL.ID and PERM.SYSTEMROLEID = @SYSTEMROLEID
  where 
    (@ONLYASSIGNED = 0 or PERM.ID is not null) and
    coalesce(SEARCHLISTSPEC.value('declare namespace bbsrch="bb_appfx_searchlist"; /bbsrch:SearchListSpec[1]/@NoSecurityRequired', 'bit'), 0) = 0 and
    cast(coalesce(SEARCHLISTSPEC.value('declare namespace bbsrch="bb_appfx_searchlist";/bbsrch:SearchListSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit) = 1
  order by 
    SL.SECURITYUIFOLDER, SL.UINAME;