USP_DATALIST_APPEALMAILINGSETUPLETTERS

CREATE procedure dbo.USP_DATALIST_APPEALMAILINGSETUPLETTERS
(
    @CHANNELCODE tinyint = null,
    @MKTASKLADDERID uniqueidentifier = null
)
as
    set nocount on;

    declare @MAXSEQUENCE int;

    select
        MKTSEGMENTATION.ID as SEGMENTATIONID,
        COMMUNICATIONLETTER.ID,
        MKTSEGMENTATION.NAME,
        COMMUNICATIONLETTER.NAME,
        COMMUNICATIONLETTER.CHANNEL,
        MKTASKLADDER.NAME as ASKLADDER,
        case when len(COMMUNICATIONLETTER.MAILCONTENTHTML) > 0 then 1 else 0 end as MAILCONTENTDEFINED,
        case when len(COMMUNICATIONLETTER.EMAILCONTENTHTML) > 0 then 1 else 0 end as EMAILCONTENTDEFINED,
        MKTSEGMENTATION.ACTIVE
    from dbo.COMMUNICATIONLETTER
    left outer join dbo.MKTSEGMENTATION
        on COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATION.ID
    left outer join dbo.MKTASKLADDER
        on COMMUNICATIONLETTER.MKTASKLADDERID = MKTASKLADDER.ID
    where COMMUNICATIONLETTER.COMMUNICATIONTYPECODE = 1
        and (@MKTASKLADDERID is null or COMMUNICATIONLETTER.MKTASKLADDERID = @MKTASKLADDERID)
        and (@CHANNELCODE is null or COMMUNICATIONLETTER.CHANNELCODE = @CHANNELCODE - 1)
    order by MKTSEGMENTATION.NAME asc, COMMUNICATIONLETTER.SEQUENCE asc