UFN_FAF_FRIENDLYURLS

CREATE FUNCTION UFN_FAF_FRIENDLYURLS
(
    @CONSTITUENTID uniqueidentifier,
    @PARETYPEID int=0
)
returns nvarchar(100)
with execute as caller
as begin
declare @BASEURL nvarchar(100)
declare @CLIENTUSERID int
DECLARE @PARTICIPANTID uniqueidentifier
declare @FRIENDLYURL nvarchar(100)
if @PARETYPEID=0 
    BEGIN
        SELECT @BASEURL=SETTING.VALUE
        ,@CLIENTUSERID=dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(REG.CONSTITUENTID)
        ,@PARTICIPANTID=REG.ID
        FROM dbo.REGISTRANT REG
        INNER JOIN dbo.EVENTEXTENSION EVE ON EVE.EVENTID=REG.EVENTID
        INNER JOIN dbo.CMSSITESETTING as SETTING ON Setting.CLIENTSITESID=EVE.CLIENTSITESID and Setting.ENUMID=11
        WHERE REG.CONSTITUENTID=@CONSTITUENTID
    END
else
    begin
        SELECT @BASEURL=SETTING.VALUE
        ,@CLIENTUSERID=dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(@CONSTITUENTID)
        ,@PARTICIPANTID=TFT.ID
        FROM dbo.TEAMFUNDRAISINGTEAM TFT
        INNER JOIN dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
        INNER JOIN dbo.EVENTEXTENSION EVE ON EVE.EVENTID=TE.EVENTID
        INNER JOIN dbo.CMSSITESETTING as SETTING ON Setting.CLIENTSITESID=EVE.CLIENTSITESID and Setting.ENUMID=11
        INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID=TFT.ID
        INNER JOIN dbo.TEAMFUNDRAISER TF ON TF.ID=TFTM.TEAMFUNDRAISERID
        WHERE TF.CONSTITUENTID=@CONSTITUENTID
    end


select @FRIENDLYURL=(case when PARTURL.VANITYURL is null THEN NULL ELSE
(@BASEURL+PARTURL.VANITYURL) END)
from dbo.VANITYURL as PARTURL
INNER join dbo.FAFFRIENDLYURLPARAMS PARTFFUP 
    ON PARTFFUP.SEQUENCEID=PARTURL.RealmID 
    AND PARTFFUP.PAGETYPE=@PARETYPEID
    AND PARTFFUP.PARTICIPANTID=@PARTICIPANTID
WHERE PARTURL.CLIENTUSERID=@CLIENTUSERID AND PARTURL.RealmTypeID=7
return @FRIENDLYURL
END