UFN_CONSTITUENT_GETSPOUSETITLE2

CREATE function dbo.UFN_CONSTITUENT_GETSPOUSETITLE2(@CONSTITUENTID uniqueidentifier)
returns nvarchar(100)
as 
begin
    declare @SPOUSEID uniqueidentifier;
    declare @TITLE2 nvarchar(100) = N'';

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

    select 
        @TITLE2 = TITLECODE.DESCRIPTION
    from
        dbo.CONSTITUENT
        inner join dbo.TITLECODE on CONSTITUENT.ID = @SPOUSEID and CONSTITUENT.ISINACTIVE = 0 and CONSTITUENT.TITLE2CODEID = TITLECODE.ID
        left outer join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    where
        DECEASEDCONSTITUENT.ID is null;

    return @TITLE2;
end