UFN_NAMEFORMAT_34

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_34](
                @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 = ''

                declare @CLASSYEAR nvarchar(3);
                declare @SPOUSECLASSYEAR nvarchar(3);

                select 
                    @CLASSYEAR = case 
                        when not [PRIMARY].CLASSOF is null and len([PRIMARY].CLASSOF) = 4 then 
                            N'''' + right(cast([PRIMARY].CLASSOF as nvarchar(4)), 2)
                        when not [PRIMARY].CLASSOF is null and len([PRIMARY].CLASSOF) = 3 then 
                            N'''' + right(cast([PRIMARY].CLASSOF as nvarchar(4)), 2)
                        when not [PRIMARY].CLASSOF is null and len([PRIMARY].CLASSOF) = 2 then 
                            N'''' + cast([PRIMARY].CLASSOF as nvarchar(4))
                        when not [PRIMARY].CLASSOF is null and len([PRIMARY].CLASSOF) = 2 then 
                            N'''' + cast([PRIMARY].CLASSOF as nvarchar(4))
                        else 
                            N'' 
                    end,
                    @SPOUSECLASSYEAR = case
                        when not [SPOUSE].CLASSOF is null and len([SPOUSE].CLASSOF) = 4 then
                            N'''' + right(cast([SPOUSE].CLASSOF as nvarchar(4)), 2)
                        when not [SPOUSE].CLASSOF is null and len([SPOUSE].CLASSOF) = 3 then 
                            N'''' + right(cast([SPOUSE].CLASSOF as nvarchar(4)), 2)
                        when not [SPOUSE].CLASSOF is null and len([SPOUSE].CLASSOF) = 2 then 
                            N'''' + cast([SPOUSE].CLASSOF as nvarchar(4))
                        when not [SPOUSE].CLASSOF is null and len([SPOUSE].CLASSOF) = 2 then 
                            N'''' + cast([SPOUSE].CLASSOF as nvarchar(4))
                        else
                            N''
                    end
                from dbo.CONSTITUENT
                left join dbo.EDUCATIONALHISTORY as [PRIMARY] on [PRIMARY].CONSTITUENTID = CONSTITUENT.ID and [PRIMARY].ISPRIMARYRECORD = 1
                left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISSPOUSE = 1
                left join dbo.EDUCATIONALHISTORY as [SPOUSE] on [SPOUSE].CONSTITUENTID = RELATIONSHIP.RECIPROcALCONSTITUENTID and [SPOUSE].ISPRIMARYRECORD = 1
                where CONSTITUENT.ID = @CONSTITUENTID;

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

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

                if len(@MIDDLENAME) > 0
                    set @result = @result + dbo.UFN_MAKEINITIALS(@MIDDLENAME) + N' '

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

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

                if (len(@SPOUSETITLE) > 0 or len(@SPOUSEFIRSTNAME) > 0 or len(@SPOUSEMIDDLENAME) > 0 or len(@SPOUSELASTNAME) > 0)
                    set @result = @result + N'and' + N'{CONDBREAK}'

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

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

                if len(@SPOUSEMIDDLENAME) > 0
                    set @result = @result + dbo.UFN_MAKEINITIALS(@SPOUSEMIDDLENAME) + N' '

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

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

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