USP_CREATEORUPDATE_CONSTITUENTDISPLAYNAME

create procedure dbo.USP_CREATEORUPDATE_CONSTITUENTDISPLAYNAME
(
    @CHANGEAGENTID uniqueidentifier = null
)
with execute as caller
as
begin

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

    declare @SQL nvarchar(max);
    declare @CREATEORALTER nvarchar(7);

    declare @INDIVIDUAL_NAMEFORMATFUNCTION nvarchar(200);
    select top (1) @INDIVIDUAL_NAMEFORMATFUNCTION = NF.FORMATSQLTABLEVALUEFUNCTION
    from dbo.CONSTITUENTDISPLAYNAMEFORMAT CDNF
        inner join dbo.NAMEFORMATFUNCTION NF on CDNF.INDIVIDUALNAMEFORMATFUNCTIONID = NF.ID
        inner join sys.objects SO on NF.FORMATSQLTABLEVALUEFUNCTION = SO.[NAME] and SO.[TYPE] = 'IF';

    if @INDIVIDUAL_NAMEFORMATFUNCTION is null
    begin
        --GenerateFunction

        declare @FORCERELOAD bit = 1;
        declare @SPEC xml;

        select @SPEC = SQLFUNCTIONSPECXML
        from dbo.SQLFUNCTIONCATALOG
        where ID = '417dc59e-545c-43a5-8679-1205e497ac0a' and FUNCTIONNAME = 'UFN_CONSTITUENT_DISPLAYNAME' ;

        exec dbo.USP_LOADSPEC @SPEC, @CHANGEAGENTID, 'USP_CREATEORUPDATE_CONSTITUENTDISPLAYNAME', '', 0 , @FORCERELOAD;
    end
    else
    begin
        if exists(select object_id from sys.objects where type = 'IF' and name = 'UFN_CONSTITUENT_DISPLAYNAME')
            set @CREATEORALTER = 'alter ';
        else
            set @CREATEORALTER = 'create ';

        if charindex('.', @INDIVIDUAL_NAMEFORMATFUNCTION) <= 0
            set @INDIVIDUAL_NAMEFORMATFUNCTION = 'dbo.' + @INDIVIDUAL_NAMEFORMATFUNCTION;

        set @SQL = @CREATEORALTER + 'function dbo.UFN_CONSTITUENT_DISPLAYNAME' + char(10);
        set @SQL = @SQL + '(@ID uniqueidentifier)' + char(10);
        set @SQL = @SQL + 'returns table as' + char(10);
        set @SQL = @SQL + 'return (' + char(10);
        set @SQL = @SQL + '  select' + char(10);
        set @SQL = @SQL + '    case C.ISORGANIZATION when 1 then' + char(10);
        set @SQL = @SQL + '       case C.KEYNAMEPREFIX when '''' then C.KEYNAME else C.KEYNAMEPREFIX + '' '' + C.KEYNAME end' + char(10);
        set @SQL = @SQL + '    else' + char(10);
        set @SQL = @SQL + '      case C.ISGROUP when 1 then' + char(10);
        set @SQL = @SQL + '        case C.DISPLAYNAME when '''' then C.KEYNAME else C.DISPLAYNAME end' + char(10);
        set @SQL = @SQL + '      else' + char(10);
        set @SQL = @SQL + '        NF.NAME' + char(10);
        set @SQL = @SQL + '      end' + char(10);
        set @SQL = @SQL + '    end as NAME' + char(10);
        set @SQL = @SQL + '  from dbo.CONSTITUENT C' + char(10);
        set @SQL = @SQL + '    outer apply ' + @INDIVIDUAL_NAMEFORMATFUNCTION + '(C.ID) as NF' + char(10);
        set @SQL = @SQL + '  where C.ID = @ID' + char(10);
        set @SQL = @SQL + ')' + char(10);

        exec (@SQL);
    end
end