UFN_RELATIONSHIPJOBINFO_GETCURRENTJOBTITLE

create function dbo.UFN_RELATIONSHIPJOBINFO_GETCURRENTJOBTITLE
(
    @RELATIONSHIPID uniqueidentifier
)
returns nvarchar(200)
with execute as caller
as begin

    declare @JOBTITLE nvarchar(200);

    select 
        @JOBTITLE = [RELATIONSHIPJOBINFO].[JOBTITLE]
    from dbo.[RELATIONSHIP]
    left join dbo.[RELATIONSHIPSET] 
        on dbo.[RELATIONSHIP].[RELATIONSHIPSETID] = [RELATIONSHIPSET].[ID]
    left join dbo.[RELATIONSHIPJOBINFO] 
        on dbo.[RELATIONSHIPSET].[ID] = [RELATIONSHIPJOBINFO].[RELATIONSHIPSETID]
        and ([RELATIONSHIPJOBINFO].[STARTDATE] <= getdate() or [RELATIONSHIPJOBINFO].[STARTDATE] is null)
        and ([RELATIONSHIPJOBINFO].[ENDDATE] >= getdate() or [RELATIONSHIPJOBINFO].[ENDDATE] is null)
    where [RELATIONSHIP].[ID] = @RELATIONSHIPID

    return @JOBTITLE;
end