spAddUpdate_EmailJob

Parameters

Parameter Parameter Type Mode Description
@PKID int INOUT
@UserID int IN
@EmailID int IN
@EmailTemplateID int IN
@ContentHTML ntext 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
@XMLDatasetAddressees ntext IN
@XMLDatasetMergeData ntext IN
@Name nvarchar(50) IN
@WSSMTPJobIdentifier uniqueidentifier IN
@WSDateLastAccessed datetime IN
@NumberRequested int IN
@NumberAbsentAddress int IN
@NumberOptedOut int IN
@NumberInvalidAccount int IN
@SendAfterDate datetime IN
@ClientIP nchar IN
@ConditionalParentJobID int IN
@MERGEDATASETSCHEMA xml IN
@AlternateSubject nvarchar(255) IN
@MERGESUBJECTSETSCHEMA xml IN

Definition

Copy 


      CREATE PROCEDURE [dbo].[spAddUpdate_EmailJob]
            (
            @PKID int output,
            @UserID    int,
            @EmailID int,
            @EmailTemplateID int,
            @ContentHTML ntext,
            @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),
            @XMLDatasetAddressees ntext,
            @XMLDatasetMergeData ntext,
            @Name nvarchar(50),
            @WSSMTPJobIdentifier uniqueidentifier,
            @WSDateLastAccessed datetime,
            @NumberRequested integer,
            @NumberAbsentAddress integer,
            @NumberOptedOut integer,
            @NumberInvalidAccount integer,
            @SendAfterDate datetime,
            @ClientIP nchar(39),
            @ConditionalParentJobID int,
            @MERGEDATASETSCHEMA xml,
              @AlternateSubject nvarchar(255) = null,
      @MERGESUBJECTSETSCHEMA xml = null
            )
            AS
            set nocount on

            DECLARE @GUID uniqueidentifier

            BEGIN TRANSACTION
            IF (@PKID<=0) BEGIN
                INSERT INTO EmailJob
                (
                EmailID,
                EmailTemplateID,
                ContentHTML,
                ContentTEXT,
                FromAddress,
                FromDisplayName,
                Subject,
                Priority,
                ReplyAddress,
                ReturnReceipt,
                ReturnReceiptAddress,
                ForwardDSN,
                ForwardDSNAddress,
                XMLDatasetAddressees,
                XMLDatasetMergeData,
                [Name],
                OwnerID,
                WSSMTPJobIdentifier,
                WSDateLastAccessed,
                NumberRequested,
                NumberAbsentAddress,
                NumberOptedOut,
                NumberInvalidAccount,
                SendAfterDate,
                ClientIP,
                ConditionalParentJobID,
                MERGEDATASETSCHEMA,
                [Status],
                 AlternateSubject,
        MERGESUBJECTSETSCHEMA
                )
                VALUES
                (
                @EmailID,
                @EmailTemplateID,
                @ContentHTML,
                @ContentTEXT,
                @FromAddress,
                @FromDisplayName,
                @Subject,
                @Priority,
                @ReplyAddress,
                @ReturnReceipt,
                @ReturnReceiptAddress,
                @ForwardDSN,
                @ForwardDSNAddress,
                @XMLDatasetAddressees,
                @XMLDatasetMergeData,
                @Name,
                @UserID,
                @WSSMTPJobIdentifier,
                @WSDateLastAccessed,
                @NumberRequested,
                @NumberAbsentAddress,
                @NumberOptedOut,
                @NumberInvalidAccount,
                @SendAfterDate,
                @ClientIP,
                @ConditionalParentJobID,
                @MERGEDATASETSCHEMA,
                0,
        @AlternateSubject,
        @MERGESUBJECTSETSCHEMA
                )
                SELECT @PKID = @@Identity
                SELECT @GUID = [GUID] from [EmailJob] WHERE [ID] = @PKID
                EXEC spAuditThis @UserID, 1, @GUID, 15

                --All of the conditional jobs should be current, not just the last one

                IF @ConditionalParentJobID Is Null
                BEGIN
                    UPDATE EmailJob
                    SET [Current] = 0
                    WHERE EmailID = @EmailID
                    AND ID <> @PKID
                    INSERT INTO EMailJobStatus(EmailID, EMailJobID, Status) VALUES(@EmailID, @PKID, 'Created')
                END
            END ELSE BEGIN
                UPDATE EmailJob SET
                EmailID = @EmailID,
                EmailTemplateID = @EmailTemplateID,
                ContentHTML = @ContentHTML,
                ContentTEXT = @ContentTEXT,
                FromAddress=@FromAddress,
                FromDisplayName=@FromDisplayName ,
                Subject=@Subject ,
                Priority=@Priority ,
                ReplyAddress=@ReplyAddress ,
                ReturnReceipt=@ReturnReceipt ,
                ReturnReceiptAddress=@ReturnReceiptAddress ,
                ForwardDSN=@ForwardDSN ,
                ForwardDSNAddress=@ForwardDSNAddress ,
                XMLDatasetAddressees = @XMLDatasetAddressees,
                XMLDatasetMergeData = @XMLDatasetMergeData,
                [Name] = @Name,
                OwnerID = @UserID,
                WSSMTPJobIdentifier = @WSSMTPJobIdentifier,
                WSDateLastAccessed = @WSDateLastAccessed,
                NumberRequested = @NumberRequested,
                NumberAbsentAddress = @NumberAbsentAddress,
                NumberOptedOut = @NumberOptedOut,
                SendAfterDate = @SendAfterDate,
                NumberInvalidAccount = @NumberInvalidAccount,
                ConditionalParentJobID = @ConditionalParentJobID,
                MERGEDATASETSCHEMA = @MERGEDATASETSCHEMA,
                AlternateSubject=@AlternateSubject,
                MERGESUBJECTSETSCHEMA=@MERGESUBJECTSETSCHEMA
                WHERE ID=@PKID
                SELECT @GUID = [GUID] from [EmailJob] WHERE [ID] = @PKID
                EXEC spAuditThis @UserID, 2, @GUID, 15
            END
            COMMIT TRANSACTION