USP_EMAILTEMPLATE_ADDUPDATE

Parameters

Parameter	Parameter Type	Mode
@PKID	int	INOUT
@UserID	int	IN
@ClientsID	int	IN
@Name	nvarchar(92)	IN
@Description	nvarchar(255)	IN
@ContentHTML	ntext	IN
@RecipientListsXML	nvarchar(4000)	IN
@ContentText	ntext	IN
@FromAddress	nvarchar(255)	IN
@FromDisplayName	nvarchar(255)	IN
@Subject	nvarchar(4000)	IN
@Priority	int	IN
@ReplyAddress	nvarchar(255)	IN
@ReturnReceipt	bit	IN
@ReturnReceiptAddress	nvarchar(255)	IN
@ForwardDSN	bit	IN
@ForwardDSNAddress	nvarchar(255)	IN
@GUID	uniqueidentifier	INOUT
@Type	tinyint	IN
@DataSourceID	int	IN
@ClientSitesID	int	IN
@AppealID	int	IN
@ExportDefinitionID	uniqueidentifier	IN
@EmailAddressTypeID	uniqueidentifier	IN
@IsExcludeInactiveRecipient	bit	IN
@InactiveRecipientFromMonths	int	IN
@AlternateSubject	nvarchar(255)	IN

Definition

Copy



CREATE PROCEDURE [dbo].[USP_EMAILTEMPLATE_ADDUPDATE]
(
    @PKID int OUTPUT,
    @UserID    int,
    @ClientsID int,
    @Name nvarchar(92),
    @Description nvarchar(255),
    @ContentHTML ntext,
    @RecipientListsXML nvarchar(4000),
    @ContentText ntext,
    @FromAddress nvarchar(255),
    @FromDisplayName nvarchar(255),
    @Subject nvarchar(4000),
    @Priority int,
    @ReplyAddress nvarchar(255),
    @ReturnReceipt bit,
    @ReturnReceiptAddress nvarchar(255),
    @ForwardDSN bit,
    @ForwardDSNAddress nvarchar(255),
    @GUID uniqueidentifier OUTPUT,
    @Type tinyint,
    @DataSourceID int,
    @ClientSitesID int,
    @AppealID int = null,
    @ExportDefinitionID uniqueidentifier = null,
    @EmailAddressTypeID uniqueidentifier = null,
  @IsExcludeInactiveRecipient bit = 0,
  @InactiveRecipientFromMonths int = 0,
    @AlternateSubject nvarchar(255) = null
)
AS
BEGIN TRANSACTION

IF (@PKID<=0)
BEGIN
    INSERT INTO [dbo].EmailTemplate
    (
        ClientsID,
        [Name],
        [Description],
        OwnerID,
        ContentHTML,
        ContentText,
        FromAddress,
        FromDisplayName,
        Subject,
        Priority,
        ReplyAddress,
        ReturnReceipt,
        ReturnReceiptAddress,
        ForwardDSN,
        ForwardDSNAddress,
        Type,
        DataSourceID,
        ClientSitesID,
        AppealID,
        ExportDefinitionID,
        EmailAddressTypeID,
    IsExcludeInactiveRecipient,
    InactiveRecipientFromMonths,
        AlternateSubject
    )
    VALUES
    (
        @ClientsID,
        @Name,
        @Description,
        @UserID,
        @ContentHTML,
        @ContentText,
        @FromAddress,
        @FromDisplayName,
        @Subject,
        @Priority,
        @ReplyAddress,
        @ReturnReceipt,
        @ReturnReceiptAddress,
        @ForwardDSN,
        @ForwardDSNAddress,
        @Type,
        @DataSourceID,
        @ClientSitesID,
        @AppealID,
        @ExportDefinitionID,
        @EmailAddressTypeID,
    @IsExcludeInactiveRecipient,
    @InactiveRecipientFromMonths,
        @AlternateSubject
    )

    SELECT @PKID = @@Identity
    SELECT @GUID = [GUID] FROM [dbo].EmailTemplate WHERE [ID] = @PKID
    EXEC spAuditThis @UserID, 1, @GUID, 14

END
ELSE
BEGIN

    UPDATE [dbo].[EmailTemplate]
    SET
        ClientsID=@ClientsID,
        [Name]=@Name,
        [Description]=@Description,
        ContentHTML=@ContentHTML,
        ContentText=@ContentText,
        FromAddress=@FromAddress,
        FromDisplayName=@FromDisplayName ,
        Subject=@Subject ,
        Priority=@Priority ,
        ReplyAddress=@ReplyAddress ,
        ReturnReceipt=@ReturnReceipt ,
        ReturnReceiptAddress=@ReturnReceiptAddress ,
        ForwardDSN=@ForwardDSN,
        ForwardDSNAddress=@ForwardDSNAddress,
        Type=@Type,
        DataSourceID=@DataSourceID,
        ClientSitesID=@ClientSitesID,
        AppealID=@AppealID,
        ExportDefinitionID=@ExportDefinitionID,
        EmailAddressTypeID = @EmailAddressTypeID,
    IsExcludeInactiveRecipient = @IsExcludeInactiveRecipient,
    InactiveRecipientFromMonths = @InactiveRecipientFromMonths,
        AlternateSubject=@AlternateSubject
    WHERE ID=@PKID

    SELECT @GUID=[GUID] FROM [dbo].[EmailTemplate] WHERE [ID] = @PKID
    EXEC spAuditThis @UserID, 2, @GUID, 14

    if @ExportDefinitionID is not null
        update dbo.EXPORTDEFINITION
        set LOCKFIELDS = 1
        where ID = @ExportDefinitionID

END

/*if this is an existing newsletter, no sense deleting/readding the link*/
if @Type <> 2 or not exists(select * from EmailTemplate_EmailList where EmailTemplateID = @PKID)
begin
/* Update the default recipients table... */
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @RecipientListsXML


DELETE FROM [dbo].EmailTemplate_EmailList WHERE EmailTemplateID = @PKID

INSERT EmailTemplate_EmailList (EmailTemplateID, EmailListID) SELECT @PKID, EmailListId FROM OPENXML (@idoc, '/root/RecipLists', 1)
WITH ( EmailListID int ) /* listid is an XML attribute defined in the source-code that generates the XML document parameter... */

EXEC sp_xml_removedocument @idoc
end
COMMIT TRANSACTION