USP_DATALIST_PARTCIPANTCOMMUNICATIONTEMPLATES

CREATE procedure dbo.USP_DATALIST_PARTCIPANTCOMMUNICATIONTEMPLATES(
  @EVENTID uniqueidentifier,
  @ROLE tinyint = null,
  @EVENTEMAILTEMPLATEID uniqueidentifier = null
)
as
    set nocount on;

    select
    EET.ID
      ,ET.NAME
      ,ET.[DESCRIPTION]
      ,ET.[SUBJECT]    
    ,ISAVAILABLETOINDIVIDUAL
    ,ISAVAILABLETOHOUSEHOLDMEMBER
    ,ISAVAILABLETOHOUSEHOLD
    ,ISAVAILABLETOTEAMMEMBER
    ,ISAVAILABLETOTEAMLEADER
    ,ISAVAILABLETOCOMPANYLEADER
    --,ISAVAILABLETOFUNDRAISINGCOACH

    --,ISAVAILABLETOSPONSOR

    ,ET.ContentHTML

    --, EET.RECIPIENTDONOR

    --, EET.RECIPIENTPROSPECT

    --, EET.RECIPIENTINDIVIDUAL

    --, EET.RECIPIENTHOUSEHOLDMEMBER

    --, EET.RECIPIENTHOUSEHOLDLEADER

    --, EET.RECIPIENTTEAMMEMBER

    --, EET.RECIPIENTTEAMLEADER

    --, EET.RECIPIENTCOMPANYLEADER

    --, EET.RECIPIENTSTATUSPREVIOUS

    --, EET.RECIPIENTSTATUSCURRENT


    from dbo.EVENTEMAILTEMPLATE EET
  join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
    where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
  AND (@ROLE is null OR 
        (
          (@ROLE = 1 AND ISAVAILABLETOINDIVIDUAL = 1) 
          or (@ROLE = 2 AND ISAVAILABLETOHOUSEHOLD = 1)           
          or (@ROLE = 3 AND ISAVAILABLETOTEAMMEMBER = 1)         
          or (@ROLE = 4 AND ISAVAILABLETOTEAMLEADER = 1)         
          or (@ROLE = 5 AND ISAVAILABLETOCOMPANYLEADER = 1)         
          or (@ROLE = 6 AND ISAVAILABLETOFUNDRAISINGCOACH = 1)         
          --or (@ROLE = 7 AND ISAVAILABLETOSPONSOR = 1)

          or (@ROLE = 7 AND ISAVAILABLETOHOUSEHOLDMEMBER = 1) 
        )
     )
  AND (EET.ID = @EVENTEMAILTEMPLATEID or @EVENTEMAILTEMPLATEID is null)