USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLE_ASSIGNGROUPS

The save procedure used by the edit dataform template "System Role Assign Groups Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@ADGROUPLIST	xml	IN	Active Directory Group List
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLE_ASSIGNGROUPS
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @ADGROUPLIST xml
                    )    
                    as
                        set nocount on;

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

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE=getdate();

                        begin try
                            update 
                                dbo.SYSTEMROLE 
                            set
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED=@CURRENTDATE
                            where 
                                ID = @ID;

                            --Parse and validate ad group list

                            declare @adgroups table (groupid varbinary(85), groupname nvarchar(255), id uniqueidentifier, includesubcontainers bit, ldapquery nvarchar(4000), searchroot nvarchar(1024), overwriteexistingsecurity bit)

                            insert into @adgroups (groupid, groupname, id, includesubcontainers, ldapquery, searchroot, overwriteexistingsecurity)
                                select distinct 
                                    GROUPID, 
                                    DISPLAYNAME, 
                                    ID, 
                                    INCLUDESUBCONTAINERS, 
                                    LDAPQUERY, 
                                    SEARCHROOT, 
                                    OVERWRITEEXISTINGSECURITY
                                from 
                                    dbo.[UFN_SYSTEMROLE_GETGROUPLIST_FROMITEMLISTXML](@ADGROUPLIST);

                            --Remove groups no longer in role

                            delete from 
                                dbo.SYSTEMROLEADGROUP
                            where 
                                SYSTEMROLEID=@ID and
                                ID not in (select ID from @adgroups);

                            --Update any groups that exist

                            update 
                                dbo.SYSTEMROLEADGROUP 
                            set 
                                GROUPID = NEWGROUPS.GROUPID, 
                                DISPLAYNAME = NEWGROUPS.GROUPNAME,
                                INCLUDESUBCONTAINERS = NEWGROUPS.INCLUDESUBCONTAINERS, 
                                LDAPQUERY = NEWGROUPS.LDAPQUERY, 
                                SEARCHROOT = NEWGROUPS.SEARCHROOT,
                                OVERWRITEEXISTINGSECURITY = NEWGROUPS.OVERWRITEEXISTINGSECURITY
                            from 
                                dbo.SYSTEMROLEADGROUP
                                inner join @adgroups as NEWGROUPS on SYSTEMROLEADGROUP.ID = NEWGROUPS.ID;

                            --Add new groups to role

                            insert into dbo.SYSTEMROLEADGROUP (SYSTEMROLEID, GROUPID, LDAPQUERY, DISPLAYNAME, SEARCHROOT, INCLUDESUBCONTAINERS, OVERWRITEEXISTINGSECURITY, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                                select 
                                    @ID, 
                                    NEWGROUPS.GROUPID, 
                                    COALESCE(NEWGROUPS.LDAPQUERY,''), 
                                    COALESCE(NEWGROUPS.GROUPNAME,''), 
                                    COALESCE(NEWGROUPS.SEARCHROOT,''), 
                                    NEWGROUPS.INCLUDESUBCONTAINERS, 
                                    NEWGROUPS.OVERWRITEEXISTINGSECURITY,
                                    @CHANGEAGENTID, 
                                    @CHANGEAGENTID, 
                                    @CURRENTDATE, 
                                    @CURRENTDATE
                                from 
                                    @adgroups as NEWGROUPS
                                where 
                                    NEWGROUPS.ID not in (select ID from dbo.SYSTEMROLEADGROUP where SYSTEMROLEID=@ID);

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