USP_CREATEORUPDATE_BUILDNAMEFORMAT

            CREATE procedure dbo.USP_CREATEORUPDATE_BUILDNAMEFORMAT
            with execute as caller
            as
                set nocount on;

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

                if exists(select object_id from sys.objects where type = 'FN' and name = 'UFN_BUILDNAMEFORMAT_INTERNAL')
                    set @CREATEORALTER = 'alter ';
                else
                    set @CREATEORALTER = 'create ';

                --NameFormatParamList

                set @SQL = @CREATEORALTER + 'function dbo.UFN_BUILDNAMEFORMAT_INTERNAL(@NAMEFORMATFUNCTIONID uniqueidentifier,@CONSTITUENTID uniqueidentifier,' + nchar(13);
                set @SQL = @SQL + '@LASTNAME nvarchar(100),@FIRSTNAME nvarchar(50),@MIDDLENAME nvarchar(50),' + nchar(13);
                set @SQL = @SQL + '@TITLE nvarchar(100),@SUFFIX nvarchar(100),@SPOUSELASTNAME nvarchar(100),' + nchar(13);
                set @SQL = @SQL + '@SPOUSEFIRSTNAME nvarchar(50),@SPOUSEMIDDLENAME nvarchar(50),' + nchar(13);
                set @SQL = @SQL + '@SPOUSETITLE nvarchar(100),@SPOUSESUFFIX nvarchar(100), @EXCLUDESPOUSEINFO bit = 0)' + nchar(13);
                set @SQL = @SQL + 'returns nvarchar(700)' + nchar(13);
                set @SQL = @SQL + 'with execute as caller' + nchar(13);
                set @SQL = @SQL + '/*This code was auto-generated by calling dbo.USP_CREATEORUPDATE_BUILDNAMEFORMAT on ' + cast(getdate() as nvarchar) + '.  */' + nchar(13);
                set @SQL = @SQL + 'as' + nchar(13) + 'begin' + nchar(13);
                set @SQL = @SQL + '/* format the name(s) based on name format */' + nchar(13);
                set @SQL = @SQL + nchar(13);

                set @SQL = @SQL + '/* remove spouse fields if need be */' + nchar(13);
                set @SQL = @SQL + 'if @EXCLUDESPOUSEINFO = 1 begin' + nchar(13);
                set @SQL = @SQL + 'set @SPOUSEFIRSTNAME = '''';' + nchar(13);                
                set @SQL = @SQL + 'set @SPOUSEMIDDLENAME = '''';' + nchar(13);
                set @SQL = @SQL + 'set @SPOUSELASTNAME = '''';' + nchar(13);
                set @SQL = @SQL + 'set @SPOUSETITLE = '''';' + nchar(13);
                set @SQL = @SQL + 'set @SPOUSESUFFIX = '''';' + nchar(13);
                set @SQL = @SQL + 'end' + nchar(13);

                declare @ID uniqueidentifier;
                declare @FORMATSQLFUNCTION nvarchar(200);
                declare @ISUSERDEFINED bit;

                declare NAMEFORMATFUNCTION_CURSOR cursor local fast_forward for
                    select
                        ID,
                        FORMATSQLFUNCTION,
                        cast(case when exists(select ID from dbo.NAMEFORMATFUNCTION_USERDEFINED where NAMEFORMATFUNCTION_USERDEFINED.ID = NAMEFORMATFUNCTION.ID) then 1 else 0 end as bit)
                    from
                        dbo.NAMEFORMATFUNCTION
                    order by
                        SEQUENCE;

                open NAMEFORMATFUNCTION_CURSOR;

                fetch next from NAMEFORMATFUNCTION_CURSOR
                into @ID, @FORMATSQLFUNCTION, @ISUSERDEFINED;

                -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

                while @@fetch_status = 0 begin

                    if @ISUSERDEFINED = 1
                        begin
                            --NameFormatParamList

                            set @SQL = @SQL + 'IF @NAMEFORMATFUNCTIONID = '''+cast(@ID as nvarchar(36))+ ''''+ nchar(13);
                            set @SQL = @SQL + 'return dbo.'+@FORMATSQLFUNCTION+'(@CONSTITUENTID,@LASTNAME,@FIRSTNAME,@MIDDLENAME,@TITLE,@SUFFIX,@SPOUSELASTNAME,@SPOUSEFIRSTNAME,@SPOUSEMIDDLENAME,@SPOUSETITLE,@SPOUSESUFFIX,@EXCLUDESPOUSEINFO)'+ nchar(13);
                            set @SQL = @SQL + nchar(13);
                        end
                    else
                        begin
                            --NameFormatParamList

                            set @SQL = @SQL + 'IF @NAMEFORMATFUNCTIONID = '''+cast(@ID as nvarchar(36))+ ''''+ nchar(13);
                            set @SQL = @SQL + 'return dbo.'+@FORMATSQLFUNCTION+'(@CONSTITUENTID,@LASTNAME,@FIRSTNAME,@MIDDLENAME,@TITLE,@SUFFIX,@SPOUSELASTNAME,@SPOUSEFIRSTNAME,@SPOUSEMIDDLENAME,@SPOUSETITLE,@SPOUSESUFFIX)'+ nchar(13);
                            set @SQL = @SQL + nchar(13);
                        end                    

                    -- This is executed as long as the previous fetch succeeds.

                    fetch next from NAMEFORMATFUNCTION_CURSOR
                    into @ID, @FORMATSQLFUNCTION, @ISUSERDEFINED;

                end

                close NAMEFORMATFUNCTION_CURSOR;
                deallocate NAMEFORMATFUNCTION_CURSOR;

                set @SQL = @SQL + 'return ''''';
                set @SQL = @SQL + nchar(13);
                set @SQL = @SQL + 'end';

                exec (@SQL);