UFN_MERCHANDISEPRODUCTINSTANCE_BUILDDESCRIPTION_2

CREATE function dbo.UFN_MERCHANDISEPRODUCTINSTANCE_BUILDDESCRIPTION_2(@ID uniqueidentifier, @INCLUDEPRODUCTNAME bit = 1)
returns nvarchar(150)
with execute as caller
as begin
    declare @OPTIONS nvarchar(150)
    select @OPTIONS = dbo.UDA_BUILDLISTWITHDELIMITER([OPTIONS].NAME, ', ')
    from (
        select top 1000 MERCHANDISEPRODUCTOPTIONVALUE.NAME --I need to top for order by. We should only have 3, but I'm future-proofing it in case we support an infinite amount in the future. No reason to limit it (yet).

        from dbo.MERCHANDISEPRODUCTOPTIONVALUE
        inner join dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE on 
            MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTOPTIONVALUEID = MERCHANDISEPRODUCTOPTIONVALUE.ID
        inner join dbo.MERCHANDISEPRODUCTOPTION on 
            MERCHANDISEPRODUCTOPTIONVALUE.MERCHANDISEPRODUCTOPTIONID = MERCHANDISEPRODUCTOPTION.ID
        where MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTINSTANCEID = @ID
        order by MERCHANDISEPRODUCTOPTION.SEQUENCE asc
    ) as [OPTIONS]

    if @INCLUDEPRODUCTNAME = 1
        begin
            set @OPTIONS = case when len(@OPTIONS) = 0 then null else @OPTIONS end

            select @OPTIONS = P.NAME + isnull(': ' + @OPTIONS, '')
            from dbo.MERCHANDISEPRODUCT MP 
            inner join dbo.PRODUCT P on MP.ID = P.ID
            inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on MPI.MERCHANDISEPRODUCTID = MP.ID
            where MPI.ID = @ID
        end
    else
        if len(@OPTIONS) = 0
            set @OPTIONS = '---';


    return @OPTIONS
end