USP_DATAFORMTEMPLATE_EDITLOAD_PARTICIPANTCOMMUNICATIONTEMPLATES2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@NAME	nvarchar(184)	INOUT
@DESCRIPTION	nvarchar(500)	INOUT
@SUBJECT	nvarchar(500)	INOUT
@USERID	int	INOUT
@CLIENTSITEID	int	INOUT
@ISAVAILABLETOINDIVIDUAL	bit	INOUT
@ISAVAILABLETOHOUSEHOLD	bit	INOUT
@ISAVAILABLETOTEAMMEMBER	bit	INOUT
@ISAVAILABLETOTEAMLEADER	bit	INOUT
@ISAVAILABLETOCOMPANYLEADER	bit	INOUT
@ISAVAILABLETOFUNDRAISINGCOACH	bit	INOUT
@ISAVAILABLETOSPONSOR	bit	INOUT
@INDIVIDUALSENABLED	bit	INOUT
@HOUSEHOLDSENABLED	bit	INOUT
@TEAMSENABLED	bit	INOUT
@COMPANIESENABLED	bit	INOUT
@FUNDRAISINGCOACHENABLED	bit	INOUT
@SPONSORENABLED	bit	INOUT
@CONTENTHTML	nvarchar(max)	INOUT
@ISACTIVE	bit	INOUT
@CONFIRMATIONTYPECODE	tinyint	INOUT
@FROMADDRESS	nvarchar(500)	INOUT
@FROMNAME	nvarchar(500)	INOUT
@REPLYADDRESS	nvarchar(500)	INOUT
@RECIPIENTDONOR	bit	INOUT
@RECIPIENTPROSPECT	bit	INOUT
@RECIPIENTINDIVIDUAL	bit	INOUT
@RECIPIENTHOUSEHOLDMEMBER	bit	INOUT
@RECIPIENTHOUSEHOLDLEADER	bit	INOUT
@RECIPIENTTEAMMEMBER	bit	INOUT
@RECIPIENTTEAMLEADER	bit	INOUT
@RECIPIENTCOMPANYLEADER	bit	INOUT
@RECIPIENTSTATUSPREVIOUS	bit	INOUT
@RECIPIENTSTATUSCURRENT	bit	INOUT
@PREVIOUSYEAREVENTID	uniqueidentifier	INOUT
@AVAILABLEASLETTER	bit	INOUT
@ISAVAILABLETOHOUSEHOLDMEMBER	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PARTICIPANTCOMMUNICATIONTEMPLATES2(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
    @NAME nvarchar(184) = null output,
    @DESCRIPTION nvarchar(500) = null output,
    @SUBJECT nvarchar(500) = null output,
  @USERID int = null output,
  @CLIENTSITEID int = null output,
  @ISAVAILABLETOINDIVIDUAL bit = null output,
    @ISAVAILABLETOHOUSEHOLD bit = null output,
    @ISAVAILABLETOTEAMMEMBER bit = null output,
    @ISAVAILABLETOTEAMLEADER bit = null output,
    @ISAVAILABLETOCOMPANYLEADER bit = null output,
    @ISAVAILABLETOFUNDRAISINGCOACH bit = null output,
  @ISAVAILABLETOSPONSOR bit = null output,
  @INDIVIDUALSENABLED bit = null output,
  @HOUSEHOLDSENABLED bit = null output,
  @TEAMSENABLED bit = null output,
  @COMPANIESENABLED bit = null output,  
  @FUNDRAISINGCOACHENABLED bit = null output,
  @SPONSORENABLED bit = null output,
  @CONTENTHTML nvarchar(max) = null output,
  @ISACTIVE bit = null output,
  @CONFIRMATIONTYPECODE tinyint = null output,
  @FROMADDRESS nvarchar(500) = null output,
  @FROMNAME nvarchar(500) = null output,
  @REPLYADDRESS nvarchar(500) = null output,

  @RECIPIENTDONOR bit = null output,
  @RECIPIENTPROSPECT bit = null output,
  @RECIPIENTINDIVIDUAL bit = null output,
  @RECIPIENTHOUSEHOLDMEMBER bit = null output,
  @RECIPIENTHOUSEHOLDLEADER bit = null output,
  @RECIPIENTTEAMMEMBER bit = null output,
  @RECIPIENTTEAMLEADER bit = null output,
  @RECIPIENTCOMPANYLEADER bit = null output,
  @RECIPIENTSTATUSPREVIOUS bit = null output,
  @RECIPIENTSTATUSCURRENT bit = null output,
  @PREVIOUSYEAREVENTID uniqueidentifier= null output,
  @AVAILABLEASLETTER bit = null output,
  @ISAVAILABLETOHOUSEHOLDMEMBER bit = null output
)
as

    set nocount on;
  declare @EVENTID uniqueidentifier
    -- be sure to set these, in case the select returns no rows

    set @DATALOADED = 0
    set @TSLONG = 0

  -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency

    -- can be considered.

    select
        @DATALOADED = 1
        ,@TSLONG = EET.TSLONG
    ,@EVENTID = EET.EVENTID
        ,@NAME = ET.NAME
        ,@DESCRIPTION = ET.DESCRIPTION
        ,@SUBJECT = ET.SUBJECT        
    ,@USERID = ET.OwnerID
    ,@CLIENTSITEID = ET.ClientSitesID
    ,@ISAVAILABLETOINDIVIDUAL = ISAVAILABLETOINDIVIDUAL
    ,@ISAVAILABLETOHOUSEHOLD = ISAVAILABLETOHOUSEHOLD
    ,@ISAVAILABLETOTEAMMEMBER = ISAVAILABLETOTEAMMEMBER
    ,@ISAVAILABLETOTEAMLEADER = ISAVAILABLETOTEAMLEADER
    ,@ISAVAILABLETOCOMPANYLEADER = ISAVAILABLETOCOMPANYLEADER
    ,@ISAVAILABLETOFUNDRAISINGCOACH = ISAVAILABLETOFUNDRAISINGCOACH
    ,@ISAVAILABLETOSPONSOR = ISAVAILABLETOSPONSOR
    ,@CONTENTHTML = ET.ContentHTML
    ,@ISACTIVE = EET.ISACTIVE
    ,@CONFIRMATIONTYPECODE = EET.CONFIRMATIONTYPECODE
    ,@FROMADDRESS = ET.FromAddress
    ,@FROMNAME = ET.FromDisplayName
    ,@REPLYADDRESS = ET.ReplyAddress
    ,@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
    ,@AVAILABLEASLETTER = EET.AVAILABLEASLETTER
    ,@ISAVAILABLETOHOUSEHOLDMEMBER = ISAVAILABLETOHOUSEHOLDMEMBER
    from dbo.EVENTEMAILTEMPLATE EET 
  join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID

    where EET.ID = @ID;

  if exists(select 1 from EVENTSPONSORSHIPTYPE where EVENTID = @EVENTID)
        set @SPONSORENABLED = 1
    else
        set @SPONSORENABLED = 0  

  select 
    @INDIVIDUALSENABLED = INDIVIDUALSFUNDRAISE,
    @HOUSEHOLDSENABLED = HOUSEHOLDSFUNDRAISE,
    @TEAMSENABLED = TEAMSFUNDRAISE,
    @COMPANIESENABLED = COMPANIESFUNDRAISE,    
    @FUNDRAISINGCOACHENABLED = CASE WHEN INDIVIDUALSFUNDRAISE = 1 OR HOUSEHOLDSFUNDRAISE = 1 OR TEAMSFUNDRAISE = 1 OR COMPANIESFUNDRAISE = 1 THEN 1 ELSE 0 END ,
    @PREVIOUSYEAREVENTID= PRIORYEAREVENTID    
  --from EVENTTEAMFAFCONFIG 

  --WHERE EVENTID = @EVENTID

    FROM dbo.EVENTEXTENSION E(NOLOCK) INNER JOIN 
      dbo.EVENTTEAMFAFCONFIG EC (NOLOCK)
      ON E.EVENTID= EC.EVENTID AND E.EVENTID=  @EVENTID

    return 0;