USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUP

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUP (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @NAME nvarchar(100),
                        @DESCRIPTION nvarchar(255) = null,
                        @IDSETREGISTERID uniqueidentifier = null,
                        @OTHERSCANMODIFY bit = 1,
                        @SITES xml = null
                    ) with execute as owner as
                        set nocount on;

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

                        if @ID is null
                            set @ID = newid();

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

                        declare @APPLOCKRESULT int;
                        declare @APPLOCKNAME nvarchar(40);
                        set @APPLOCKNAME = 'USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUP';

                        begin try
                            declare @SELECTIONEXISTS bit;
                            set @SELECTIONEXISTS = 0;

                            --check for object, then drop

                            if object_id('tempdb..#ADD_RESEARCHGROUP_MEMBERSTOINSERT') is not null
                                drop table #ADD_RESEARCHGROUP_MEMBERSTOINSERT;

                            create table #ADD_RESEARCHGROUP_MEMBERSTOINSERT
                            (
                                CONSTITUENTID uniqueidentifier
                            );

                            --Prepare members selection for insert

                            declare @DBOBJECTNAME nvarchar(128);
                            declare @DBOBJECTTYPE smallint;

                            select
                                @DBOBJECTNAME = IDSETREGISTER.DBOBJECTNAME,
                                @DBOBJECTTYPE = IDSETREGISTER.OBJECTTYPE
                            from
                                dbo.IDSETREGISTER
                            where
                                IDSETREGISTER.ID = @IDSETREGISTERID;

                            if not @DBOBJECTNAME is null and @DBOBJECTNAME <> '' begin
                                set @SELECTIONEXISTS = 1;

                                if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                                else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @ID) + ''')';

                                declare @SQLTOEXEC nvarchar(max);
                                set @SQLTOEXEC = N'set nocount on;

                                insert into #ADD_RESEARCHGROUP_MEMBERSTOINSERT (
                                    CONSTITUENTID
                                ) select
                                    CONSTITUENT.ID
                                from ' + nchar(13);

                                set @SQLTOEXEC = @SQLTOEXEC + @DBOBJECTNAME + N' as SELECTION
                                left join dbo.[CONSTITUENT] on CONSTITUENT.[ID] = SELECTION.[ID]
                                where CONSTITUENT.ISORGANIZATION = 0 and CONSTITUENT.ISGROUP = 0' + nchar(13);

                                exec sp_executesql @SQLTOEXEC;
                            end

                            --get applock                            

                            exec @APPLOCKRESULT = sp_getapplock @Resource=@APPLOCKNAME, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=0

                            exec dbo.USP_RESEARCHGROUP_VALIDATESITES @SITES, @CURRENTAPPUSERID, 'A4245C3E-08FD-4E56-AC78-18B4EE77AA6E', 1;

                            if @APPLOCKRESULT = 0 begin
                                --create Research Group

                                insert into dbo.RESEARCHGROUP (
                                    ID,
                                    NAME,
                                    DESCRIPTION,
                                    OWNERID,
                                    OTHERSCANMODIFY,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                values (
                                    @ID,
                                    @NAME,
                                    @DESCRIPTION,
                                    @CURRENTAPPUSERID,
                                    @OTHERSCANMODIFY,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );

                                if not @SITES is null
                                    exec dbo.USP_RESEARCHGROUP_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;

                                if @SELECTIONEXISTS = 1 begin
                                    --insert members and update Research Group numbers

                                    insert into dbo.RESEARCHGROUPMEMBER (
                                        RESEARCHGROUPID,
                                        CONSTITUENTID,
                                        ADDEDBYID,
                                        CHANGEDBYID,
                                        DATEADDED,
                                        DATECHANGED
                                    ) select
                                        @ID,
                                        CONSTITUENTID,
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID,
                                        @CURRENTDATE,
                                        @CURRENTDATE
                                    from
                                        #ADD_RESEARCHGROUP_MEMBERSTOINSERT;

                                    exec sp_releaseapplock @Resource=@APPLOCKNAME, @LockOwner='Session';

                                    exec dbo.USP_RESEARCHGROUP_RECALCULATENUMMEMBERS @ID, @CHANGEAGENTID;
                                end
                                else begin
                                    exec sp_releaseapplock @Resource=@APPLOCKNAME, @LockOwner='Session';
                                end
                            end
                        end try
                        begin catch
                            exec sp_releaseapplock @Resource=@APPLOCKNAME, @LockOwner='Session';

                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;