USP_DATAFORMTEMPLATE_ADD_VOLUNTEER

The save procedure used by the add dataform template "Volunteer Add Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CONSTITUENTID	uniqueidentifier	IN	Record
@EMERGENCYCONTACTNAME	nvarchar(100)	IN	Name
@EMERGENCYCONTACTPHONE	nvarchar(100)	IN	Phone
@SPONSORID	uniqueidentifier	IN	Sponsor
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_VOLUNTEER
                    (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CONSTITUENTID uniqueidentifier = null,    
                        @EMERGENCYCONTACTNAME nvarchar(100) = null,
                        @EMERGENCYCONTACTPHONE nvarchar(100) = null,
                        @SPONSORID uniqueidentifier = null
                    )
                    as
                        set nocount on;

                        declare @CURRENTDATE datetime;

                        set @ID = @CONSTITUENTID;

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

                        set @CURRENTDATE = getdate();

                        declare @CURRENTDATEEARLIESTTIME datetime;
                        set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);

                        begin try
                            if exists (select 1 from dbo.VOLUNTEER where ID = @ID)
                                update dbo.VOLUNTEER set
                                    SPONSORID = @SPONSORID,
                                    EMERGENCYCONTACTNAME = @EMERGENCYCONTACTNAME,
                                    EMERGENCYCONTACTPHONE = @EMERGENCYCONTACTPHONE,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where
                                    ID = @ID
                                    and 
                                    (
                                        SPONSORID <> @SPONSORID
                                        or
                                        EMERGENCYCONTACTNAME <> @EMERGENCYCONTACTNAME
                                        or
                                        EMERGENCYCONTACTPHONE <> @EMERGENCYCONTACTPHONE
                                    );
                            else
                                insert into dbo.VOLUNTEER(ID, SPONSORID, EMERGENCYCONTACTNAME, EMERGENCYCONTACTPHONE, 
                                                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@ID, @SPONSORID, @EMERGENCYCONTACTNAME, @EMERGENCYCONTACTPHONE,
                                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                            --Clean up old dates

                            declare @contextCache varbinary(128);
                            /* cache current context information */
                            set @contextCache = CONTEXT_INFO();
                            /* set CONTEXT_INFO to @CHANGEAGENTID */
                            if not @CHANGEAGENTID is null
                                set CONTEXT_INFO @CHANGEAGENTID

                            delete
                                dbo.VOLUNTEERDATERANGE
                            where
                                CONSTITUENTID = @ID and
                                (DATEFROM >= @CURRENTDATEEARLIESTTIME or DATETO is null);

                            /* reset CONTEXT_INFO to previous value */
                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache

                            --Insert new dates

                            insert into dbo.VOLUNTEERDATERANGE
                            (
                                CONSTITUENTID,
                                DATEFROM,
                                DATETO,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @ID,
                                @CURRENTDATEEARLIESTTIME,
                                null,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );
                        end try

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

                        return 0;