USP_DATAFORMTEMPLATE_EDIT_QUERYVIEW_PERMISSIONS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_QUERYVIEW_PERMISSIONS
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SYSTEMROLELIST xml
)
as begin
  set nocount on;

  declare @CONTEXTCACHE varbinary(128);

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  /* cache current context information */
  set @CONTEXTCACHE = CONTEXT_INFO();

  /* set CONTEXT_INFO to @CHANGEAGENTID */
  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID

  begin try
    -- build a temporary table containing the values from the XML, excluding ones where permission is None
    -- not using UFN_QUERYVIEW_GETSYSTEMROLEPERMISSIONSLIST_FROMITEMLISTXML since we need 
    -- GRANTORDENY to be deserialized as a tinyint (because it could be set to 2, which indicates None)
    declare @TEMP table (ID uniqueidentifier, SYSTEMROLEID uniqueidentifier, GRANTORDENY bit)
    insert into @TEMP 
    select
      T.c.value('(ID)[1]','uniqueidentifier') as ID,
      T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') as SYSTEMROLEID,
      T.c.value('(GRANTORDENY)[1]','bit') as GRANTORDENY
    from @SYSTEMROLELIST.nodes('/SYSTEMROLELIST/ITEM') T(c)
    where 
      T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1);

    -- rebuild the system role list
    set @SYSTEMROLELIST =
      (select ID, SYSTEMROLEID, GRANTORDENY
       from @TEMP
       for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), binary base64);

    if @SYSTEMROLELIST is null
      delete from dbo.SYSTEMROLEPERM_QUERYVIEW where QUERYVIEWCATALOGID = @ID;
    else
      exec dbo.USP_QUERYVIEW_GETSYSTEMROLEPERMISSIONSLIST_UPDATEFROMXML @ID, @SYSTEMROLELIST, @CHANGEAGENTID;

    /* reset CONTEXT_INFO to previous value */
    if not @CONTEXTCACHE is null
      set CONTEXT_INFO @CONTEXTCACHE
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end