USP_BBNC_COMMITPROFILEUPDATENAMEFORMAT

CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATENAMEFORMAT
                (
                    @CONSTITUENTID uniqueidentifier = null,    
          @NAMEFORMATID uniqueidentifier = null,
                    @NAMEFORMATTYPECODEID uniqueidentifier = null,
          @NAMEFORMATFUNCTIONID uniqueidentifier = null,
                    @CUSTOMNAME nvarchar(1400) = '',
                    @ISPRIMARYADDRESSEE bit = null,
                    @ISPRIMARYSALUTATION bit = null,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @CHANGEDATE datetime = null
                ) as
                    set nocount on;

                    declare @SEQUENCE int;

                    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 @PRIMARYADDRESSEEID uniqueidentifier;
          declare @PRIMARYSALUTATIONID uniqueidentifier;

                    begin try
            if @ISPRIMARYADDRESSEE = 1
              begin
                select 
                  @PRIMARYADDRESSEEID = [ID]
                from 
                  dbo.NAMEFORMAT
                where
                  [CONSTITUENTID] = @CONSTITUENTID
                  and [PRIMARYADDRESSEE] = 1;

                if @PRIMARYADDRESSEEID is not null and (@NAMEFORMATID is null or @PRIMARYADDRESSEEID <> @NAMEFORMATID)
                  begin
                    update dbo.NAMEFORMAT set
                      [PRIMARYADDRESSEE] = 0
                    where
                      [ID] = @PRIMARYADDRESSEEID;
                  end            
              end

            if @ISPRIMARYSALUTATION = 1
              begin
                select 
                  @PRIMARYSALUTATIONID = [ID]
                from 
                  dbo.NAMEFORMAT
                where
                  [CONSTITUENTID] = @CONSTITUENTID
                  and [PRIMARYSALUTATION] = 1;

                if @PRIMARYSALUTATIONID is not null and (@NAMEFORMATID is null or @PRIMARYSALUTATIONID <> @NAMEFORMATID)
                  begin
                    update dbo.NAMEFORMAT set
                      [PRIMARYSALUTATION] = 0
                    where
                      [ID] = @PRIMARYSALUTATIONID;
                  end            
            end

            if @NAMEFORMATID is null
              begin
                set @NAMEFORMATID = newid();

                insert into dbo.NAMEFORMAT
                (
                  [ID],
                                    [CONSTITUENTID],
                                    [NAMEFORMATTYPECODEID],
                                    [NAMEFORMATFUNCTIONID],
                                    [PRIMARYADDRESSEE],
                                    [PRIMARYSALUTATION],
                                    [CUSTOMNAME],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED]
                                )
                                values
                                (
                                    @NAMEFORMATID,
                                    @CONSTITUENTID,
                                    @NAMEFORMATTYPECODEID,
                                    @NAMEFORMATFUNCTIONID,
                                    @ISPRIMARYADDRESSEE,
                                    @ISPRIMARYSALUTATION,
                                    @CUSTOMNAME,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CHANGEDATE,
                                    @CHANGEDATE
                                );  
              end
            else
              begin
                                    update dbo.NAMEFORMAT set
                    [CONSTITUENTID] = @CONSTITUENTID,
                    [NAMEFORMATTYPECODEID] = @NAMEFORMATTYPECODEID,
                                        [NAMEFORMATFUNCTIONID] = @NAMEFORMATFUNCTIONID,
                                        [PRIMARYADDRESSEE] = @ISPRIMARYADDRESSEE,
                                        [PRIMARYSALUTATION] = @ISPRIMARYSALUTATION,
                                        [CUSTOMNAME] = @CUSTOMNAME,
                                        [CHANGEDBYID] = @CHANGEAGENTID,
                                        [DATECHANGED] = @CHANGEDATE
                                    where
                                        ID = @NAMEFORMATID;
              end


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

                    return 0;