USP_SEARCHLIST_COMMUNICATIONLETTER

Search for communication letters.

Parameters

Parameter	Parameter Type	Mode	Description
@COMMUNICATIONTYPECODE	tinyint	IN	Communication type code
@SEGMENTATIONNAME	nvarchar(100)	IN	Communication
@LETTERNAME	nvarchar(100)	IN	Name
@CHANNELCODE	tinyint	IN	Send via
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_COMMUNICATIONLETTER
(
    @COMMUNICATIONTYPECODE tinyint,
    @SEGMENTATIONNAME nvarchar(100) = null,
    @LETTERNAME nvarchar(100) = null,
    @CHANNELCODE tinyint = null,
    @MAXROWS smallint = 500
)
as    
    select top(@MAXROWS)
        COMMUNICATIONLETTER.ID,
        MKTSEGMENTATION.NAME,
        COMMUNICATIONLETTER.NAME,
        COMMUNICATIONLETTER.CHANNEL,
        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
    from dbo.COMMUNICATIONLETTER
    left outer join dbo.MKTSEGMENTATION
        on COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATION.ID
    where (@COMMUNICATIONTYPECODE = 0 or COMMUNICATIONLETTER.COMMUNICATIONTYPECODE = @COMMUNICATIONTYPECODE)
        and (@SEGMENTATIONNAME is null or MKTSEGMENTATION.NAME like '%' + @SEGMENTATIONNAME + '%')
        and (@LETTERNAME is null or COMMUNICATIONLETTER.NAME like '%' + @LETTERNAME + '%')
        and (@CHANNELCODE is null or COMMUNICATIONLETTER.CHANNELCODE = @CHANNELCODE - 1)
    order by MKTSEGMENTATION.NAME asc, COMMUNICATIONLETTER.NAME asc