UFN_CONSTITUENT_GETSPOUSENICKNAME

Find the Spouse Nickname for a given constituent

Return

Return Type
nvarchar(50)

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_CONSTITUENT_GETSPOUSENICKNAME(@CONSTITUENTID uniqueidentifier)
            returns nvarchar(50)
            as 
            begin
                declare @SPOUSEID uniqueidentifier;
                declare @NICKNAME nvarchar(50) = N'';

                select top 1 @SPOUSEID = RELATIONSHIP.RECIPROCALCONSTITUENTID 
                from dbo.RELATIONSHIP 
                where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1

                select @NICKNAME = NICKNAME
                from
                    dbo.CONSTITUENT
                    left outer join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = @SPOUSEID
                where
                    CONSTITUENT.ID = @SPOUSEID
                    and CONSTITUENT.ISINACTIVE = 0
                    and DECEASEDCONSTITUENT.ID is null;

                return @NICKNAME;
            end