UFN_FAFPARTICIPANTEMAILCONTACTTYPES

            CREATE function dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES
            (@EMAILTEMPLATEID int
           --  @ROLETYPE tinyint -- 0:Participant, 1:Team Leader, 2:Company Leader, 3:Household , 4:Team Member, 5:Coach

            )
            returns nvarchar(1000)
            with execute as caller
            as begin

              DECLARE @CONTACTTYPE nvarchar(1000)

                DECLARE @RECIPIENTDONOR bit,
                    @RECIPIENTPROSPECT bit,
                    @RECIPIENTINDIVIDUAL bit,
                    @RECIPIENTHOUSEHOLDMEMBER bit,
                    @RECIPIENTHOUSEHOLDLEADER bit,
                    @RECIPIENTTEAMMEMBER bit,
                    @RECIPIENTTEAMLEADER bit,
                    @RECIPIENTCOMPANYLEADER bit,
                    @RECIPIENTSTATUSPREVIOUS bit,
                    @RECIPIENTSTATUSCURRENT bit

              SELECT @RECIPIENTDONOR = EET.RECIPIENTDONOR,
                     @RECIPIENTPROSPECT = EET.RECIPIENTPROSPECT,
                     @RECIPIENTINDIVIDUAL = EET.RECIPIENTINDIVIDUAL,
                     @RECIPIENTHOUSEHOLDMEMBER = EET.RECIPIENTHOUSEHOLDMEMBER,
                     @RECIPIENTHOUSEHOLDLEADER = EET.RECIPIENTHOUSEHOLDLEADER,
                     @RECIPIENTTEAMMEMBER = EET.RECIPIENTTEAMMEMBER,
                     @RECIPIENTTEAMLEADER = EET.RECIPIENTTEAMLEADER,
                     @RECIPIENTCOMPANYLEADER = EET.RECIPIENTCOMPANYLEADER,
                     @RECIPIENTSTATUSPREVIOUS = EET.RECIPIENTSTATUSPREVIOUS,
                     @RECIPIENTSTATUSCURRENT = EET.RECIPIENTSTATUSCURRENT
              FROM dbo.EVENTEMAILTEMPLATE EET WITH (NOLOCK)
              WHERE EET.EMAILTEMPLATEID = @EMAILTEMPLATEID


              SET @CONTACTTYPE = ''

              IF ISNULL(@RECIPIENTDONOR,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE = 'Donors - previous'

              IF ISNULL(@RECIPIENTDONOR,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Donors - current'

              IF ISNULL(@RECIPIENTPROSPECT,0) = 1 
              SET @CONTACTTYPE =  @CONTACTTYPE+'Contacts'

              IF ISNULL(@RECIPIENTINDIVIDUAL,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Individuals - previous'

              IF ISNULL(@RECIPIENTINDIVIDUAL,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Individuals - current'

              IF ISNULL(@RECIPIENTHOUSEHOLDMEMBER,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Household members - previous'

              IF ISNULL(@RECIPIENTHOUSEHOLDMEMBER,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Household members - current'

              IF ISNULL(@RECIPIENTHOUSEHOLDLEADER,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Household leaders - previous'

              IF ISNULL(@RECIPIENTHOUSEHOLDLEADER,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Household leaders - current'

              IF ISNULL(@RECIPIENTTEAMMEMBER,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Team members - previous'

              IF ISNULL(@RECIPIENTTEAMMEMBER,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Team members - current'

              IF ISNULL(@RECIPIENTTEAMLEADER,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Team leaders - previous'

              IF ISNULL(@RECIPIENTTEAMLEADER,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Team leaders - current'

              IF ISNULL(@RECIPIENTCOMPANYLEADER,0) = 1 AND ISNULL(@RECIPIENTSTATUSPREVIOUS,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Company leaders - previous'

              IF ISNULL(@RECIPIENTCOMPANYLEADER,0) = 1 AND ISNULL(@RECIPIENTSTATUSCURRENT,0) = 1
              SET @CONTACTTYPE =  @CONTACTTYPE+'Company leaders - current'

              SET @CONTACTTYPE = REPLACE(@CONTACTTYPE, 'current', 'current;')

              SET @CONTACTTYPE = REPLACE(@CONTACTTYPE, 'Contacts', 'Contacts;')

              SET @CONTACTTYPE = REPLACE(@CONTACTTYPE, 'previous', 'previous;')

              IF LEN(@CONTACTTYPE) > 2
              SET @CONTACTTYPE = Substring(@CONTACTTYPE,1,len(@CONTACTTYPE)-1)

              RETURN @CONTACTTYPE
            end