USP_REPORT_EMAILSTATUSDETAILGENERALPURPOSE

Parameters

Parameter	Parameter Type	Mode
@JobDescription	nvarchar(1000)	IN
@EmailSubject	nvarchar(1000)	IN
@Status	int	IN
@StartDate	datetime	IN
@EndDate	datetime	IN
@SortBy	int	IN
Definition

Copy


            CREATE procedure dbo.USP_REPORT_EMAILSTATUSDETAILGENERALPURPOSE
            (
                @JobDescription as nvarchar(1000),
                @EmailSubject as nvarchar(1000),
                @Status as int,
                @StartDate as datetime,
                @EndDate as datetime,
                @SortBy int
            )
            as
            begin
                select
                    esg.EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') as JobDescription
                    ,esg.EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') as EmailSubject
                    ,esg.[STATUS]
                    ,esg.DATECHANGED
                    ,esg.DATEADDED
                from 
                    dbo.EMAILSTATUSGENERALPURPOSE esg
                where
                    ((@JobDescription = '') or (esg.EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') like (@JobDescription + '%')))
                    and ((@EmailSubject = '') or (esg.EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') like (@EmailSubject + '%')))
                    and ((@Status is null) or (esg.[STATUS] = @Status))
                    and 
                        --Sort by DateAdded

                        ((@SortBy = 1 and 
                            ((@StartDate is null) or (esg.DATEADDED >= dbo.UFN_DATE_GETEARLIESTTIME(@StartDate))) and
                            ((@EndDate is null) or (esg.DATEADDED <= dbo.UFN_DATE_GETLATESTTIME(@EndDate))))
                        or
                        --Sort by DateChanged

                        (@SortBy = 2 and
                            ((@StartDate is null) or (esg.DATECHANGED >= dbo.UFN_DATE_GETEARLIESTTIME(@StartDate))) and
                            ((@EndDate is null) or (esg.DATECHANGED <= dbo.UFN_DATE_GETLATESTTIME(@EndDate)))));
            end