UFN_CONSTITUENT_GETSPOUSENICKNAME_TVF

CREATE function dbo.UFN_CONSTITUENT_GETSPOUSENICKNAME_TVF(@CONSTITUENTID uniqueidentifier)
returns table
as 
return
(
    select
        SPOUSE.NICKNAME as VALUE
    from dbo.RELATIONSHIP RP with(nolock)
        inner join dbo.CONSTITUENT SPOUSE with(nolock) on RP.RECIPROCALCONSTITUENTID = SPOUSE.ID and SPOUSE.ISINACTIVE = 0
        left outer join dbo.DECEASEDCONSTITUENT with(nolock) on DECEASEDCONSTITUENT.ID = SPOUSE.ID
    where
        RP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
        and RP.ISSPOUSE = 1
        and DECEASEDCONSTITUENT.ID is null
)