USP_EMAIL_JOBSEARCH

        CREATE procedure dbo.USP_EMAIL_JOBSEARCH
        (
          @SEARCHTRANSACTIONALJOBS bit,
          @SEARCHGENERALPURPOSEJOBS bit,
            @SUBJECT nvarchar(1000) = null,
          @EMAILADDAFTERDATE datetime = null,
          @EMAILADDBEFOREDATE datetime = null,
          @JOBDESCRIPTION nvarchar(1000) = null,
          @STATUS integer = null,
            @MAXROWS smallint = 500
        )
        as
            set @SUBJECT = COALESCE(@SUBJECT,'') + '%';        
          set @JOBDESCRIPTION = COALESCE(@JOBDESCRIPTION,'') + '%';
          set @EMAILADDAFTERDATE = COALESCE(@EMAILADDAFTERDATE,cast('1980-01-01' as datetime));
          set @EMAILADDBEFOREDATE = COALESCE(@EMAILADDBEFOREDATE,cast('3000-01-01' as datetime));
          set @STATUS = COALESCE(@STATUS,9); -- default to all jobs


          /*
            Status to StatusCode mapping
            StatusCode = 0 (Queued)
            Status = 15
            StatusCode = 1 (Processing)
            Status = 1
            Status = 2
            Status = 7
            Status = 10
            StatusCode = 2 (Completed)
            Status = 3
            StatusCode = 3 (Canceled)
            Status = 5
            StatusCode = 4 (Deleted)
            Status = 18
            StatusCode = 5 (Stopped / Paused)
            Status = 6
            Status = 8
            StatusCode = 6 (Error)
            Status = 0
            Status = 4
            Status = 9
            Status = 16
            Status = 17  
          */



          with EMAILJOBSTATUS_CTE(ID,
                                  JOBTYPE,
                                  JOBDESCRIPTION,
                                  [SUBJECT],
                                  PROCESSINGORDER,
                                              RETRYNUMBER,
                                              [STATUS],
                                  DATEADDED,
                                              DATECHANGED,
                                  REQUESTEDSENT,
                                  ACTUALSENT
                                             )
           as (select ID,
                      0,
                            EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') as JOBDESCRIPTION,
                      EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') as [SUBJECT],
                      PRIORITY,
                            RETRYNUMBER,
                            [STATUS],
                      DATEADDED,
                            DATECHANGED,
                      REQUESTEDSENT,
                      ACTUALSENT
                from dbo.EMAILSTATUSGENERALPURPOSE
                where @SEARCHGENERALPURPOSEJOBS = 1

                union all

                select ID,
                       1,
                             EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') as JOBDESCRIPTION,
                       EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') as [SUBJECT],
                         0,
                         RETRYNUMBER,
                         [STATUS],
                       DATEADDED,
                       DATECHANGED,
                       REQUESTEDSENT,
                       ACTUALSENT
                from dbo.EMAILSTATUSTRANSACTIONAL
                where @SEARCHTRANSACTIONALJOBS = 1
                )

            select top (@MAXROWS) ID,
                             JOBTYPE,
                                     JOBDESCRIPTION,
                             [SUBJECT],
                                     PROCESSINGORDER,
                                     RETRYNUMBER,
                                     [STATUS],
                             DATEADDED,
                                     DATECHANGED,
                             REQUESTEDSENT,
                             ACTUALSENT
            from EMAILJOBSTATUS_CTE 
              where
                   (SUBJECT like @SUBJECT)
               and
       (JOBDESCRIPTION like @JOBDESCRIPTION)
               and
               (DATEADDED >= @EMAILADDAFTERDATE)
               and
               (DATEADDED <= @EMAILADDBEFOREDATE)
               and
               (@STATUS = (case when [STATUS] = 15 then 0
                                                  when [STATUS] = 3 then 2
                                                  when [STATUS] = 18 then 4
                                                  when [STATUS] in (6,8) then 5
                                                  when [STATUS] in (1,2,7,10) then 1
                                                  else 6 end
                                    )
                                    or
                                    (@STATUS = 7 and [STATUS] in (1,2,7,10,15))
                                    or
                                    (@STATUS = 8 and [STATUS] in (3,6,8,18))
                                    or
                                    (@STATUS = 9)
                                    )
              order by 
                  DATECHANGED desc