UFN_DAILYSALEITEM_BUILDMEMBERSHIPBUTTONTEXT

CREATE function dbo.UFN_DAILYSALEITEM_BUILDMEMBERSHIPBUTTONTEXT
(
    @ID uniqueidentifier, 
    @CURRENCYSYMBOL nchar(1)
)

returns nvarchar(65)
with execute as caller
as begin
    declare @LINESEPARATOR nchar(1) = char(10)

    declare @DESCRIPTIONTYPECODE1 tinyint
    declare @DESCRIPTIONTYPECODE2 tinyint
    declare @DESCRIPTIONTYPECODE3 tinyint

    declare @DESCRIPTION1 nvarchar(20)
    declare @DESCRIPTION2 nvarchar(20)
    declare @DESCRIPTION3 nvarchar(20)

    select
        @DESCRIPTIONTYPECODE1 = DESCRIPTIONFIELD1TYPECODE,
        @DESCRIPTIONTYPECODE2 = DESCRIPTIONFIELD2TYPECODE,
        @DESCRIPTIONTYPECODE3 = DESCRIPTIONFIELD3TYPECODE,
        @DESCRIPTION1 = DESCRIPTIONFIELD1,
        @DESCRIPTION2 = DESCRIPTIONFIELD2,
        @DESCRIPTION3 = DESCRIPTIONFIELD3
    from dbo.DAILYSALEITEM
    where ID = @ID

    declare @PROGRAMNAME nvarchar(100)
    declare @LEVELNAME nvarchar(100)
    declare @TERMDESCRIPTION nvarchar(100)
    declare @TERMPRICE nvarchar(11)
    declare @CUSTOM nvarchar(100)

    select 
        @PROGRAMNAME = [MEMBERSHIPPROGRAM].[NAME],
        @LEVELNAME = [MEMBERSHIPLEVEL].[NAME],
        @TERMDESCRIPTION = dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE([MEMBERSHIPLEVELTERM].ID),
        @TERMPRICE = @CURRENCYSYMBOL + convert(nvarchar(10),[MEMBERSHIPLEVELTERM].AMOUNT)
    from dbo.DAILYSALEITEMMEMBERSHIP
    inner join dbo.MEMBERSHIPLEVELTERM
        on DAILYSALEITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
    inner join dbo.MEMBERSHIPLEVEL
        on DAILYSALEITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    inner join dbo.MEMBERSHIPPROGRAM
        on  DAILYSALEITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
    where DAILYSALEITEMMEMBERSHIP.ID = @ID

    select 
        @DESCRIPTION1 = case @DESCRIPTIONTYPECODE1 
                when 2 then @PROGRAMNAME
                when 4 then @TERMPRICE 
                when 5 then @LEVELNAME
                when 6 then @TERMDESCRIPTION
                when 7 then @TERMDESCRIPTION + ' ' + @TERMPRICE
            else
                @DESCRIPTION1
            end,
        @DESCRIPTION2 = case @DESCRIPTIONTYPECODE2
                when 2 then @PROGRAMNAME
                when 4 then @TERMPRICE 
                when 5 then @LEVELNAME
                when 6 then @TERMDESCRIPTION
                when 7 then @TERMDESCRIPTION + ' ' + @TERMPRICE
            else
                @DESCRIPTION2
            end,
        @DESCRIPTION3 = case @DESCRIPTIONTYPECODE3
                when 2 then @PROGRAMNAME
                when 4 then @TERMPRICE 
                when 5 then @LEVELNAME
                when 6 then @TERMDESCRIPTION
                when 7 then @TERMDESCRIPTION + ' ' + @TERMPRICE
            else
                @DESCRIPTION3
            end

    return isnull(@DESCRIPTION1, '') + @LINESEPARATOR + isnull(@DESCRIPTION2, '') + @LINESEPARATOR + isnull(@DESCRIPTION3, '')
end