USP_DATALIST_CAMPAIGNDOCUMENTATION

This datalist returns all documentation for a campaign.
Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DOCUMENTATIONTYPECODE	tinyint	IN	Documentation type
@TITLE	nvarchar(50)	IN	Title
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CAMPAIGNDOCUMENTATION 
                (
                    @CAMPAIGNID uniqueidentifier = null,
                    @DOCUMENTATIONTYPECODE tinyint = null,
                    @TITLE nvarchar(50) = ''
                )
                as
                    set nocount on;

                    select    
                        CAMPAIGNNOTE.ID,
                        'C54FBCDF-1A3F-4BCB-8303-3AF53D146D0D' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        CAMPAIGNNOTE.DATEENTERED,
                        CAMPAIGNNOTETYPECODE.DESCRIPTION as [TYPE],
                        CAMPAIGNNOTE.TITLE,
                        CONSTITUENT.NAME
                    from
                        dbo.CAMPAIGNNOTE
                    inner join
                        dbo.CAMPAIGNNOTETYPECODE
                    on
                        CAMPAIGNNOTETYPECODE.ID = CAMPAIGNNOTE.CAMPAIGNNOTETYPECODEID
                    left outer join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = CAMPAIGNNOTE.AUTHORID
                    where 
                        CAMPAIGNNOTE.CAMPAIGNID = @CAMPAIGNID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or CAMPAIGNNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        CAMPAIGNMEDIALINK.ID,
                        'C90ED3ED-902D-4AB6-8C79-E43D7061E822' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        CAMPAIGNMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        CAMPAIGNMEDIALINK.DATEENTERED,
                        CAMPAIGNMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        CAMPAIGNMEDIALINK.TITLE,
                        CONSTITUENT.NAME
                    from
                        dbo.CAMPAIGNMEDIALINK
                    inner join
                        dbo.CAMPAIGNMEDIALINKTYPECODE
                    on
                        CAMPAIGNMEDIALINKTYPECODE.ID = CAMPAIGNMEDIALINK.CAMPAIGNMEDIALINKTYPECODEID
                    left outer join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = CAMPAIGNMEDIALINK.AUTHORID
                    where 
                        CAMPAIGNMEDIALINK.CAMPAIGNID = @CAMPAIGNID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or CAMPAIGNMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        CAMPAIGNATTACHMENT.ID,
                        '74D74F40-83CE-4859-A75C-7CD2BB400598' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        CAMPAIGNATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        CAMPAIGNATTACHMENT.DATEENTERED,
                        CAMPAIGNATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        CAMPAIGNATTACHMENT.TITLE,
                        CONSTITUENT.NAME
                    from
                        dbo.CAMPAIGNATTACHMENT
                    inner join
                        dbo.CAMPAIGNATTACHMENTTYPECODE
                    on
                        CAMPAIGNATTACHMENTTYPECODE.ID = CAMPAIGNATTACHMENT.CAMPAIGNATTACHMENTTYPECODEID
                    left outer join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = CAMPAIGNATTACHMENT.AUTHORID
                    where 
                        CAMPAIGNATTACHMENT.CAMPAIGNID = @CAMPAIGNID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or CAMPAIGNATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc;