spEmailList_Subscriptions

CREATE    PROCEDURE [dbo].[spEmailList_Subscriptions]( 
      @UserID int, 
      @BackOfficePeopleID int,
      @EmailAddress nvarchar(255),
      @ClientSitesID int = NULL
)
AS

if @BackOfficePeopleID > 0 
begin

      select 
            l.ID EmailListID,
            l.[Name],
            l.[Description],
            s.UserID,
            s.BackOfficeSystemPeopleID,
            COALESCE(s.HasOpted, 0) [Subscribed],
            Type,
            s.id SubscriberID,
            s.FirstName,
            s.LastName,
            COALESCE(s.HasOpted, 1) [SubscribedChapter]
      from [dbo].EmailList l
            LEFT OUTER JOIN [dbo].EmailList_Subscription s 
            ON l.[ID] = s.EmailListID and s.BackOfficeSystemPeopleID =@BackOfficePeopleID
--                (select BackOfficePeopleID from vwBackofficeLinkedUsers where userID=dbo.fnGetUserIDFromLinkedPeopleID(@BackOfficePeopleID))
      WHERE Subscription = 1 
            AND l.Deleted = 0
            AND (@ClientSitesID is null 
                OR @ClientSitesID = l.ClientSitesID)


end
else
begin

      if @userID > 0 
      begin

            select 
                  l.ID EmailListID,
                  l.[Name],
                  l.[Description],
                  s.UserID,
                  s.BackOfficeSystemPeopleID,
                  COALESCE(s.HasOpted, 0) [Subscribed],
                  Type,
                  s.id SubscriberID,
                  s.FirstName,
                  s.LastName,
                  COALESCE(s.HasOpted, 1) [SubscribedChapter]
            from [dbo].EmailList l
                  LEFT OUTER JOIN [dbo].EmailList_Subscription s ON l.[ID] = s.EmailListID and s.UserID = @UserID
            WHERE Subscription = 1 
                  AND l.Deleted = 0
                  AND (@ClientSitesID is null 
                    OR @ClientSitesID = l.ClientSitesID)

      end
      else
      begin
            select 
                  l.ID EmailListID,
                  l.[Name],
                  l.[Description],
                  s.UserID,
                  s.BackOfficeSystemPeopleID,
                  COALESCE(s.HasOpted, 0) [Subscribed],
                  Type,
                  s.id SubscriberID,
                  s.FirstName,
                  s.LastName,
                  COALESCE(s.HasOpted, 1) [SubscribedChapter]
            from [dbo].EmailList l
                  LEFT OUTER JOIN [dbo].EmailList_Subscription s ON l.[ID] = s.EmailListID and s.EmailAddress = @EmailAddress
            WHERE Subscription = 1 
                  AND l.Deleted = 0
                  AND (@ClientSitesID is null
                    OR @ClientSitesID = l.ClientSitesID)

      end
end