UFN_CONSTITUENT_GETSPOUSETITLE2_TVF

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