USP_APPUSER_WITHPROXY_UPDATEPROXYPERMISSIONS

        CREATE procedure dbo.USP_APPUSER_WITHPROXY_UPDATEPROXYPERMISSIONS
                        (@APPUSERID uniqueidentifier,
                        @APPUSERSITES xml,
                        @APPUSERSYSTEMROLEID uniqueidentifier,
                        @APPUSERSECURITYMODECODE tinyint,
                        @APPUSERBRANCHSITEID uniqueidentifier,
                        @APPUSERCONSTITUENTSECURITYMODECODE tinyint,
                        @APPUSERCONSTITUENTSECURITYGROUPS xml,
                        @PROXYCOUNT int,
                        @CHANGEAGENTID uniqueidentifier,
                        @CURRENTDATE datetime)
        as
        begin
          declare @LISTCOUNT int = 1;
          declare @DYNAMICSITES xml = @APPUSERSITES;
          declare @DYNAMICCONSTITUENTSECURITYGROUPS xml = @APPUSERCONSTITUENTSECURITYGROUPS;
          declare @SYSTEMROLEAPPUSERIDPROXY uniqueidentifier;
          declare @PROXYUSERIDLIST table (PROXYCOUNT int identity(1,1), PROXYSYSTEMROLEAPPUSERID uniqueidentifier,PROXYAPPUSERID uniqueidentifier);

        insert into @PROXYUSERIDLIST
          select
            SYSTEMROLEAPPUSER.ID,
            SYSTEMROLEAPPUSER.APPUSERID
          from dbo.SYSTEMROLEAPPUSER
          inner join dbo.APPUSER on APPUSER.ID = SYSTEMROLEAPPUSER.APPUSERID
          where APPUSER.PROXYOWNERID = @APPUSERID and SYSTEMROLEAPPUSER.SYSTEMROLEID = @APPUSERSYSTEMROLEID;

          begin try
            declare @PROXYSITE table (SITEID uniqueidentifier);
            insert into @PROXYSITE
            select
              t.x.value('SITEID[1]','uniqueidentifier') 
            from @DYNAMICSITES.nodes('/SITES/ITEM') t(x);

            declare @PROXYCONSTITUENTSECURITY table (CONSTITUENTSECURITYATTRIBUTEID uniqueidentifier);
            insert into @PROXYCONSTITUENTSECURITY
            select
              t.c.value('(CONSTITUENTSECURITYATTRIBUTEID)[1]','uniqueidentifier')
            from @DYNAMICCONSTITUENTSECURITYGROUPS.nodes('/CONSTITUENTSECURITYGROUPS/ITEM') t(c)

              update SYSTEMROLEAPPUSER
                set
                  SYSTEMROLEAPPUSER.SECURITYMODECODE = @APPUSERSECURITYMODECODE,
                  SYSTEMROLEAPPUSER.DATECHANGED = @CURRENTDATE,
                  SYSTEMROLEAPPUSER.CHANGEDBYID = @CHANGEAGENTID,
                  SYSTEMROLEAPPUSER.BRANCHSITEID = @APPUSERBRANCHSITEID,
                  SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = @APPUSERCONSTITUENTSECURITYMODECODE
                from dbo.SYSTEMROLEAPPUSER
                inner join dbo.APPUSER on APPUSER.ID = SYSTEMROLEAPPUSER.APPUSERID
                where APPUSER.PROXYOWNERID = @APPUSERID and SYSTEMROLEAPPUSER.SYSTEMROLEID = @APPUSERSYSTEMROLEID;

            while (@LISTCOUNT <= @PROXYCOUNT)
            begin

               --Get the systemroleappuser ID to apply to the USP

              select @SYSTEMROLEAPPUSERIDPROXY = PROXYLIST.PROXYSYSTEMROLEAPPUSERID from @PROXYUSERIDLIST PROXYLIST where PROXYLIST.PROXYCOUNT = @LISTCOUNT;

              declare @PROXYSITESXML xml = (select null as [ID], [SITEID] from @PROXYSITE for xml raw('ITEM'), type, elements, root('SITES'), BINARY BASE64);
              declare @PROXYCONSTITUENTSECURITYXML xml = (select [CONSTITUENTSECURITYATTRIBUTEID], null as [ID] from @PROXYCONSTITUENTSECURITY for xml raw('ITEM'),type,elements,root('CONSTITUENTSECURITYGROUPS'),BINARY BASE64);

              exec dbo.USP_SYSTEMROLEAPPUSER_GETSITES_UPDATEFROMXML @SYSTEMROLEAPPUSERIDPROXY , @PROXYSITESXML, @CHANGEAGENTID, @CURRENTDATE;

              exec dbo.USP_SYSTEMROLEAPPUSER_GETSECURITYGROUPS_UPDATEFROMXML @SYSTEMROLEAPPUSERIDPROXY, @PROXYCONSTITUENTSECURITYXML, @CHANGEAGENTID, @CURRENTDATE;

              set @LISTCOUNT = @LISTCOUNT + 1;
            end

          end try
          begin catch
            exec dbo.USP_RAISE_ERROR;
          end catch
        end