USP_DATAFORMTEMPLATE_EDITLOAD_QUERYVIEW_PERMISSIONS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_QUERYVIEW_PERMISSIONS
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @FEATURENAME nvarchar(60) = null output,
  @SYSTEMROLELIST xml = null output
)
as begin
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @TSLONG = TSLONG,
    @FEATURENAME = DISPLAYNAME,
    @SYSTEMROLELIST =
      (select 
        SR.ID as SYSTEMROLEID, SR.NAME, 
        coalesce((select ID 
              from dbo.SYSTEMROLEPERM_QUERYVIEW as SRP 
              where (SRP.SYSTEMROLEID = SR.ID) and (SRP.QUERYVIEWCATALOGID = @ID)), NewID()) as ID,
        coalesce((select GRANTORDENY
              from dbo.SYSTEMROLEPERM_QUERYVIEW as SRP 
              where (SRP.SYSTEMROLEID = SR.ID) and (SRP.QUERYVIEWCATALOGID = @ID)), 2) as GRANTORDENY
      from
        dbo.SYSTEMROLE as SR
      order by SR.NAME
      for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), binary base64)
  from
    dbo.QUERYVIEWCATALOG
  where
    ID = @ID;

  return 0;
end