USP_BBNC_COMMITPROFILEUPDATEEMAIL

            CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEMAIL
                (
                    @CONSTITUENTID uniqueidentifier = null,                            
                    @EMAILADDRESSTYPECODEID uniqueidentifier = null,
                    @EMAILADDRESS nvarchar(100) = '',
                    @CHANGEAGENTID uniqueidentifier = null,
                    @CHANGEDATE datetime = null
                ) as
                    set nocount on;

                    declare @ID uniqueidentifier;
                    declare @SEQUENCE int;
                    declare @PRIMARY bit;

                    if @CONSTITUENTID is null
                        begin
                            raiserror('The constituent ID is required',16,1);
                            return -2;
                        end

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

                    if @CHANGEDATE is null
                        set @CHANGEDATE = getdate();

          declare @DEFAULTEMAILADDRESSTYPECODEID uniqueidentifier;
          select top (1)
                        @DEFAULTEMAILADDRESSTYPECODEID = [EMAILADDRESSTYPECODEID]
                    from
                        dbo.NETCOMMUNITYDEFAULTCODEMAP;


                    if @EMAILADDRESSTYPECODEID is not null
                        select @ID = [ID]
                        from dbo.EMAILADDRESS
                        where [CONSTITUENTID] = @CONSTITUENTID and [EMAILADDRESSTYPECODEID] = @EMAILADDRESSTYPECODEID;

                    declare @INFOSOURCECODEID uniqueidentifier;
                    select top (1)
                        @INFOSOURCECODEID = [INFOSOURCECODEID]
                    from
                        dbo.NETCOMMUNITYDEFAULTCODEMAP;

                    begin try
                        if @ID is not null
                            update dbo.EMAILADDRESS set
                                [EMAILADDRESSTYPECODEID] = @EMAILADDRESSTYPECODEID,
                                [EMAILADDRESS] = @EMAILADDRESS,
                                [INFOSOURCECODEID] = @INFOSOURCECODEID,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CHANGEDATE

                            where
                                [ID] = @ID
                        else
                            begin
                                select @SEQUENCE = max([SEQUENCE]) 
                                from dbo.EMAILADDRESS
                                where [CONSTITUENTID] = @CONSTITUENTID;

                                if @SEQUENCE is null
                                    begin
                                        set @SEQUENCE = 1
                                        set @PRIMARY = 1
                                    end
                                else
                                    set @PRIMARY = 0;

                                insert into dbo.EMAILADDRESS
                                (
                                    [ID],
                                    [CONSTITUENTID],
                                    [EMAILADDRESSTYPECODEID],
                                    [EMAILADDRESS],
                                    [ISPRIMARY],
                                    [SEQUENCE],
                                    [INFOSOURCECODEID],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED]
                                )
                                values
                                (
                                    newid(),
                                    @CONSTITUENTID,
                                    coalesce(@EMAILADDRESSTYPECODEID, @DEFAULTEMAILADDRESSTYPECODEID),
                                    @EMAILADDRESS,
                                    @PRIMARY,
                                    @SEQUENCE + 1,
                                    @INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CHANGEDATE,
                                    @CHANGEDATE
                                );

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

                    return 0;