USP_BBNC_NAMEFORMATS_ADDUPDATE

            CREATE procedure dbo.USP_BBNC_NAMEFORMATS_ADDUPDATE
            (
                @CONSTITUENTID uniqueidentifier,
                @XMLITEMS xml = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on;

                if (@XMLITEMS is null) return 0;

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

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

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

                -- Build a temporary table to hold the name format values from XMLITEMS

                declare @NAMEFORMATS table (
                    [TYPE] nvarchar(max),
                    [VALUE] nvarchar(max),
                    [DEFAULTFUNCTION] uniqueidentifier
                );

                insert into @NAMEFORMATS 
                    select 
                        [TYPE],[VALUE],null
                    from 
                        dbo.UFN_BBNC_NAMEFORMATS_FROMITEMLISTXML(@XMLITEMS);

                update @NAMEFORMATS 
                    set DEFAULTFUNCTION = [NAMEFORMATFUNCTIONID]
                    from dbo.NAMEFORMATDEFAULT x inner join dbo.NAMEFORMATTYPECODE y on x.NAMEFORMATTYPECODEID = y.ID 
                    where y.[DESCRIPTION] = [TYPE] and x.APPLYTOCODE = 0

                begin try
                    if exists(select 1 from @NAMEFORMATS)
                    begin
                        -- Say Mrs Brown wants to be called "Brown Family" in the annual report (while "Annual report" is the name format type name)

                        -- case 1: the database does not have an entry of "Annual report" type for Mrs Brown

                        -- if Mrs Brown sent a blank value, we apply the system default [NAMEFORMATFUNCTIONID]

                        insert into dbo.[NAMEFORMAT]
                            ([CONSTITUENTID]
                            ,[NAMEFORMATTYPECODEID]
                            ,[NAMEFORMATFUNCTIONID]
                            ,[CUSTOMNAME]
                            ,[ADDEDBYID]
                            ,[CHANGEDBYID]
                            ,[DATEADDED]
                            ,[DATECHANGED])
                        select
                            @CONSTITUENTID
                            ,a.ID
                            ,case when temp.[VALUE] <> '' then NULL else temp.DEFAULTFUNCTION end
                            ,case when temp.[VALUE] <> '' then temp.[VALUE] else '' end
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CHANGEDATE
                            ,@CHANGEDATE
                        from @NAMEFORMATS temp
                        inner join dbo.NAMEFORMATTYPECODE a on a.[DESCRIPTION] = temp.[TYPE] 
                        where not exists (
                            select 1 from dbo.NAMEFORMAT f 
                            inner join dbo.NAMEFORMATTYPECODE c on c.ID = f.NAMEFORMATTYPECODEID 
                            inner join @NAMEFORMATS temp1 on temp1.[TYPE] = c.[DESCRIPTION]
                            where f.CONSTITUENTID = @CONSTITUENTID
                        )
                  and (temp.[VALUE] <> '' or temp.DEFAULTFUNCTION is not null);

                        -- case 2: the database has an existing entry of "Annual report" type for Mrs Brown

                        update dbo.[NAMEFORMAT] 
                        set 
                            [NAMEFORMATFUNCTIONID] = case when temp.[VALUE] <> '' then NULL else b.[NAMEFORMATFUNCTIONID] end
                            ,[CUSTOMNAME] = case when temp.[VALUE] <> '' then temp.[VALUE] else '' end
                            ,[ADDEDBYID] = @CHANGEAGENTID
                            ,[CHANGEDBYID] = @CHANGEAGENTID
                            ,[DATEADDED] = @CHANGEDATE
                            ,[DATECHANGED] = @CHANGEDATE
                        from @NAMEFORMATS temp
                        inner join dbo.NAMEFORMATTYPECODE a on a.[DESCRIPTION] = temp.[TYPE] 
                        inner join dbo.NAMEFORMATDEFAULT b on b.NAMEFORMATTYPECODEID = a.ID 
                        where 
                        exists (
                            select 1 from dbo.NAMEFORMAT f 
                            inner join dbo.NAMEFORMATTYPECODE c on c.ID = f.NAMEFORMATTYPECODEID 
                            inner join @NAMEFORMATS temp1 on temp1.[TYPE] = c.[DESCRIPTION]
                            where f.CONSTITUENTID = @CONSTITUENTID
                        ) 
                        and [NAMEFORMAT].NAMEFORMATTYPECODEID = (select ID from dbo.NAMEFORMATTYPECODE where NAMEFORMATTYPECODE.[DESCRIPTION] = temp.[TYPE])
                        and b.APPLYTOCODE = 0 and [NAMEFORMAT].CONSTITUENTID = @CONSTITUENTID;
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;