USP_DATALIST_APPUSERPROXYUSERNAMESYSTEMROLE

CREATE procedure dbo.USP_DATALIST_APPUSERPROXYUSERNAMESYSTEMROLE
(
  @SYSTEMROLEAPPUSERID uniqueidentifier
)
as
  set nocount on;

  select
  APPUSER.ID as [PROXYUSERID],
  case
  when isnull(APPUSER.CUSTOM_AUTHENTICATION_USERID, '') = '' then coalesce(APPUSER.USERNAME, APPUSER.DISPLAYNAME)
  else APPUSER.CUSTOM_AUTHENTICATION_USERID
  end as [NAME],
  SYSTEMROLEAPPUSER.APPUSERID
  from dbo.SYSTEMROLEAPPUSER
  join dbo.APPUSER on SYSTEMROLEAPPUSER.APPUSERID = APPUSER.PROXYOWNERID
  join dbo.SYSTEMROLEAPPUSER PROXYUSERLIST on PROXYUSERLIST.APPUSERID = APPUSER.ID and PROXYUSERLIST.SYSTEMROLEID = SYSTEMROLEAPPUSER.SYSTEMROLEID
  where SYSTEMROLEAPPUSER.ID = @SYSTEMROLEAPPUSERID
  order by APPUSER.CUSTOM_AUTHENTICATION_USERID, APPUSER.DISPLAYNAME;