USP_DATALIST_EOPTIONMERGEDATA

CREATE procedure dbo.USP_DATALIST_EOPTIONMERGEDATA
(
  @EVENTID uniqueidentifier
  ,@CONSTITUENTID uniqueidentifier
)
as
set nocount on;

declare @html nvarchar(4000)
set @html='<table>';

select @html= @html + '<tr><td>'+ Name+': </td>' +
'<td  width=150px align=center>'+(case Subscribed when 1 then 'Yes' else 'No' end)+'</td></tr>'
from 
(
Select    l.ID EmailListID,   l.[Name],   l.[Description],   s.UserID,   
          s.BackOfficeSystemPeopleID,   COALESCE(s.HasOpted, 0) [Subscribed],   Type,   s.id SubscriberID
    from [dbo].EmailList l   LEFT OUTER JOIN [dbo].EmailList_Subscription s    
         ON l.[ID] = s.EmailListID and 
         s.BackOfficeSystemPeopleID = (Select bosp.ID
        FROM dbo.ClientUsers cu    
            INNER JOIN dbo.BackOfficeSystemUsers bosu   
              ON cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0    
            INNER JOIN dbo.BackOfficeSystemPeople bosp   
              ON bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0    
            INNER JOIN dbo.CONSTITUENT C  
              ON bosp.BackofficeRecordID = C.SEQUENCEID
        Where C.ID = @CONSTITUENTID)
WHERE Subscription = 1    AND l.Deleted = 0 and Type=0
) as NewsLetterTable

set @html=@html+'</table>'

select EOPTIONS =@html