USP_EMAIL_GETCONTENT

      CREATE procedure dbo.USP_EMAIL_GETCONTENT
      (
        @ID uniqueidentifier
      )
      as
      begin
          set nocount on;

        declare @ISGENERALPURPOSE bit;

        if (select top 1 1 from dbo.EMAILSTATUSGENERALPURPOSE where ID = @ID) = 1
        begin

           select S.EMAILHEADER, S.STATUSMESSAGE, S.UNIQUEIDFIELDNAME, S.EMAILADDRESSFIELDNAME, S.EMAILDISPLAYNAMEFIELDNAME, S.MERGETOKEN,
            Q.MERGEROWSPROCESSED, isnull(Q.PROCESSAFTERDATE,''), Q.CONTENTHTML, Q.CONTENTTEXT, '' RECIPIENTDATA,
            V.VIEWNAME, S.LASTMERGEROWPROCESSED, G.NAME, S.CONSUMERSTARTDATE, S.CONSUMERENDDATE, S.CLIENTPROCESSENDDATE, S.CLIENTPROCESSSTARTDATE,
            '' as RECIPIENTADDRESS
           from dbo.EMAILSTATUSGENERALPURPOSE S
           left join dbo.EMAILQUEUEGENERALPURPOSE Q on Q.ID = S.EMAILQUEUEGENERALPURPOSEID
           left join dbo.SQLVIEWCATALOG V on Q.MERGESQLVIEWID = V.ID 
           left join dbo.GLOBALCHANGE G on S.INTERNALJOBIDENTITY = G.ID
           where S.ID = @ID;

        end
        else
        begin
           select S.EMAILHEADER, S.STATUSMESSAGE, S.UNIQUEIDFIELDNAME, S.EMAILADDRESSFIELDNAME, S.EMAILDISPLAYNAMEFIELDNAME, S.MERGETOKEN,
            0 MERGEROWSPROCESSED, cast('19810101' as date) PROCESSAFTERDATE, Q.CONTENTHTML, Q.CONTENTTEXT, Q.RECIPIENTDATA,
            '' VIEWNAME, -1 LASTMERGEROWPROCESSED, G.NAME, S.CONSUMERSTARTDATE, S.CONSUMERENDDATE, S.CLIENTPROCESSENDDATE, S.CLIENTPROCESSSTARTDATE,
            S.RECIPIENTADDRESS
            from dbo.EMAILSTATUSTRANSACTIONAL S
            left join dbo.EMAILQUEUETRANSACTIONAL Q on Q.ID = S.EMAILQUEUETRANSACTIONALID
            left join dbo.GLOBALCHANGE G on S.INTERNALJOBIDENTITY = G.ID
            where S.ID = @ID;
        end

      end