USP_CONSTITUENTUPDATEBATCH_ADDUPDATENAMEFORMATS

create procedure dbo.USP_CONSTITUENTUPDATEBATCH_ADDUPDATENAMEFORMATS (
    @CONSTITUENTID uniqueidentifier,
    @NAMEFORMATSXML xml = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
) as begin
    set nocount on;

    if (@NAMEFORMATSXML 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();

    begin try
        merge
            dbo.[NAMEFORMAT] as TARGET
        using
            (
                select
                    NF.[NAMEFORMATTYPECODEID], NF.[CUSTOMNAME], NFD.[NAMEFORMATFUNCTIONID]
                from
                    dbo.UFN_BATCHCONSTITUENTUPDATE_GETNAMEFORMATS_FROMITEMLISTXML(@NAMEFORMATSXML) NF
                left join
                    dbo.[NAMEFORMATDEFAULT] NFD on NFD.[NAMEFORMATTYPECODEID] = NF.[NAMEFORMATTYPECODEID] and NFD.[APPLYTOCODE] = 0
                where
                    NF.[CUSTOMNAME] <> '' or NFD.[NAMEFORMATFUNCTIONID] is not null
            ) as SOURCE ([NAMEFORMATTYPECODEID], [CUSTOMNAME], [DEFAULTFUNCTIONID])
        on
            (TARGET.[NAMEFORMATTYPECODEID] = SOURCE.[NAMEFORMATTYPECODEID] and TARGET.[CONSTITUENTID] = @CONSTITUENTID)
        when matched and TARGET.FORMATTEDNAME <> SOURCE.[CUSTOMNAME] then
            update set
                [NAMEFORMATFUNCTIONID] = case when SOURCE.[CUSTOMNAME] <> '' then NULL else SOURCE.[DEFAULTFUNCTIONID] end,
                [CUSTOMNAME] = coalesce(SOURCE.[CUSTOMNAME], ''),
                [ADDEDBYID] = @CHANGEAGENTID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATEADDED] = @CHANGEDATE,
                [DATECHANGED] = @CHANGEDATE
        when not matched then
            insert (
                [CONSTITUENTID],
                [NAMEFORMATTYPECODEID],
                [NAMEFORMATFUNCTIONID],
                [CUSTOMNAME],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
            )
            values (
                @CONSTITUENTID,
                SOURCE.NAMEFORMATTYPECODEID,
                case when SOURCE.[CUSTOMNAME] <> '' then NULL else SOURCE.DEFAULTFUNCTIONID end,
                coalesce(SOURCE.[CUSTOMNAME], ''),
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE
            );
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end