UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION

CREATE function dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION
(@MEMBERSHIPLEVELTERMID uniqueidentifier)
returns nvarchar(max)
as
begin
    declare @paymentoptiondesc nvarchar(max)
    select @paymentoptiondesc = 
        case MP.PROGRAMTYPECODE
            when 0 then
                case TERMLENGTHCODE
                    when 0 then cast(TERMTIMELENGTH as nvarchar(5)) + ' month'
                    when 1 then cast(TERMTIMELENGTH as nvarchar(5)) + ' year'
                end
            when 1 then MLT.RECURRINGPAYMENTOPTION
            when 2 then 
                case MLT.LIFETIMEPAYMENTOPTIONCODE
                    when 0 then MLT.LIFETIMEPAYMENTOPTION
                    when 1 then CAST(MLT.LIFETIMENUMBEROFPAYMENTS as nvarchar(5)) + ' month'
                    when 2 then CAST(MLT.LIFETIMENUMBEROFPAYMENTS as nvarchar(5)) + ' quarter'
                    when 3 then CAST(MLT.LIFETIMENUMBEROFPAYMENTS as nvarchar(5)) + ' year'
                end
        end
    from dbo.MEMBERSHIPLEVEL ML
        join dbo.MEMBERSHIPLEVELTERM MLT ON ML.ID = MLT.LEVELID
        join dbo.MEMBERSHIPPROGRAM MP ON MP.ID = ML.MEMBERSHIPPROGRAMID
    where MLT.ID = @MEMBERSHIPLEVELTERMID

    return @paymentoptiondesc
end