UFN_DAILYSALEITEM_BUILDPROGRAMBUTTONTEXT

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

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

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

    select
        @DESCRIPTION1 =
            case
                when DESCRIPTIONFIELD1TYPECODE = 2 then
                    PROGRAM.NAME
                when DESCRIPTIONFIELD1TYPECODE = 3 then
                    PRICETYPECODE.DESCRIPTION
                when DESCRIPTIONFIELD1TYPECODE = 4 then
                    @CURRENCYSYMBOL + convert(nvarchar(19), PROGRAMPRICE.FACEPRICE)
                when DESCRIPTIONFIELD1TYPECODE = 13 then
                    @CURRENCYSYMBOL + convert(nvarchar(19), PROGRAMPRICE.FACEPRICE) + ' (' + convert(nvarchar(10),PROGRAM.CAPACITY) + ')'
                when DESCRIPTIONFIELD1TYPECODE in (14,16,17) then
                    DAILYSALEITEM.DESCRIPTIONFIELD1TYPE
                when DESCRIPTIONFIELD1TYPECODE = 15 then
                    convert(nvarchar(10), PROGRAM.CAPACITY)
                else
                    DAILYSALEITEM.DESCRIPTIONFIELD1
            end,
        @DESCRIPTION2 =
            case
                when DESCRIPTIONFIELD2TYPECODE = 2 then
                    PROGRAM.NAME
                when DESCRIPTIONFIELD2TYPECODE = 3 then
                    PRICETYPECODE.DESCRIPTION
                when DESCRIPTIONFIELD2TYPECODE = 4 then
                    @CURRENCYSYMBOL + convert(nvarchar(19), PROGRAMPRICE.FACEPRICE)
                when DESCRIPTIONFIELD2TYPECODE = 13 then
                    @CURRENCYSYMBOL + convert(nvarchar(19), PROGRAMPRICE.FACEPRICE) + ' (' + convert(nvarchar(10),PROGRAM.CAPACITY) + ')'
                when DESCRIPTIONFIELD2TYPECODE in (14,16,17) then
                    DAILYSALEITEM.DESCRIPTIONFIELD2TYPE
                when DESCRIPTIONFIELD2TYPECODE = 15 then
                    convert(nvarchar(10), PROGRAM.CAPACITY)
                else
                    DAILYSALEITEM.DESCRIPTIONFIELD2
            end,
        @DESCRIPTION3 =
            case
                when DESCRIPTIONFIELD3TYPECODE = 2 then
                    PROGRAM.NAME
                when DESCRIPTIONFIELD3TYPECODE = 3 then
                    PRICETYPECODE.DESCRIPTION
                when DESCRIPTIONFIELD3TYPECODE = 4 then
                    @CURRENCYSYMBOL + convert(nvarchar(19), PROGRAMPRICE.FACEPRICE)
                when DESCRIPTIONFIELD3TYPECODE = 13 then
                    @CURRENCYSYMBOL + convert(nvarchar(19), PROGRAMPRICE.FACEPRICE) + ' (' + convert(nvarchar(10),PROGRAM.CAPACITY) + ')'
                when DESCRIPTIONFIELD3TYPECODE in (14,16,17)
                    then DAILYSALEITEM.DESCRIPTIONFIELD3TYPE
                when DESCRIPTIONFIELD3TYPECODE = 15 then
                    convert(nvarchar(10), PROGRAM.CAPACITY)
                else
                    DAILYSALEITEM.DESCRIPTIONFIELD3
            end
    from
        dbo.DAILYSALEITEM
    inner join
        dbo.DAILYSALEITEMPROGRAM on DAILYSALEITEMPROGRAM.ID = DAILYSALEITEM.ID
    inner join
        dbo.PROGRAM on PROGRAM.ID = DAILYSALEITEMPROGRAM.PROGRAMID
    inner join
        dbo.PRICETYPECODE on PRICETYPECODE.ID = DAILYSALEITEMPROGRAM.PRICETYPECODEID
    inner join
        dbo.PROGRAMPRICE on PROGRAMPRICE.PROGRAMID = DAILYSALEITEMPROGRAM.PROGRAMID and PROGRAMPRICE.PRICETYPECODEID = DAILYSALEITEMPROGRAM.PRICETYPECODEID
    where
        DAILYSALEITEM.ID = @ID

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