UFN_MEMBERSHIP_GETMIDTERMUPGRADEPRICE

CREATE function dbo.UFN_MEMBERSHIP_GETMIDTERMUPGRADEPRICE
(
    @MEMBERSHIPID uniqueidentifier,
    @MEMBERSHIPLEVELID uniqueidentifier
)
returns money
with execute as caller
as begin
    declare @CURRENTTERMLENGTHCODE tinyint
    declare @CURRENTTERMTIMELENGTH tinyint
    declare @CURRENTTERMPRICE money
    declare @UPGRADEPRICE money

    select
        @CURRENTTERMLENGTHCODE = MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
        @CURRENTTERMTIMELENGTH = MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
        @CURRENTTERMPRICE = MEMBERSHIPLEVELTERM.AMOUNT
    from
        dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
    where MEMBERSHIP.ID = @MEMBERSHIPID

    select @UPGRADEPRICE = AMOUNT - @CURRENTTERMPRICE
    from dbo.MEMBERSHIPLEVELTERM
    where
        LEVELID = @MEMBERSHIPLEVELID and
        TERMLENGTHCODE = @CURRENTTERMLENGTHCODE and
        TERMTIMELENGTH = @CURRENTTERMTIMELENGTH

    if @UPGRADEPRICE < 0
        return 0

    return @UPGRADEPRICE
end