UFN_NAMEFORMAT_32

Household default name format.

Return

Return Type
nvarchar(700)

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@LASTNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@TITLE	nvarchar(100)	IN
@SUFFIX	nvarchar(100)	IN
@SPOUSELASTNAME	nvarchar(100)	IN
@SPOUSEFIRSTNAME	nvarchar(50)	IN
@SPOUSEMIDDLENAME	nvarchar(50)	IN
@SPOUSETITLE	nvarchar(100)	IN
@SPOUSESUFFIX	nvarchar(100)	IN

Definition

Copy



            CREATE function [dbo].[UFN_NAMEFORMAT_32](
                @CONSTITUENTID uniqueidentifier,
                @LASTNAME nvarchar(100),
                @FIRSTNAME nvarchar(50),
                @MIDDLENAME nvarchar(50),
                @TITLE nvarchar(100),
                @SUFFIX nvarchar(100),
                @SPOUSELASTNAME nvarchar(100),
                @SPOUSEFIRSTNAME nvarchar(50),
                @SPOUSEMIDDLENAME nvarchar(50),
                @SPOUSETITLE nvarchar(100),
                @SPOUSESUFFIX nvarchar(100))
            returns nvarchar(700)
            with execute as caller
            as
            begin
                declare @result nvarchar(700)
                set @result = ''

                if (@LASTNAME = @SPOUSELASTNAME) and (len(@FIRSTNAME) > 0) and (len(@SPOUSEFIRSTNAME) > 0)
                    set @result = @FIRSTNAME + N' and ' + @SPOUSEFIRSTNAME + N' ' + @LASTNAME;
                else
                begin
                    if (len(@SPOUSEFIRSTNAME) > 0) or (len(@SPOUSELASTNAME) > 0)
                    begin
                        if len(@FIRSTNAME) > 0
                            set @result = @result + @FIRSTNAME + N' '

                        if len(@LASTNAME) > 0
                            set @result = @result + @LASTNAME + N' '

                        if len(@SUFFIX) > 0
                            set @result = @result + @SUFFIX + N' '

                        set @result = @result + 'and '
                            if len(@SPOUSEFIRSTNAME) > 0
                            set @result = @result + @SPOUSEFIRSTNAME + N' '

                        if len(@SPOUSELASTNAME) > 0
                            set @result = @result + @SPOUSELASTNAME + N' '

                        if len(@SPOUSESUFFIX) > 0
                            set @result = @result + @SPOUSESUFFIX + N' '
                    end
                    else
                    begin
                        set @result = @LASTNAME + N' ' + (select DESCRIPTION from dbo.CONSTITUENTTYPECODE where ID='5777F3C1-FBC1-47F7-BF69-4789DC29D958')
                    end
                end    

                return replace(rtrim(ltrim(@result)), N' ,', N',')
            end