spBulkUpdate_EmailJob

CREATE PROCEDURE [dbo].[spBulkUpdate_EmailJob]
(
    @XML as ntext
)

AS

create table #temp(    DSNRecipientID int,
    [JobIdentity] uniqueidentifier,
    JobStatus int,
    DateModified datetime
)

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @XML

INSERT INTO #Temp(JobIdentity, JobStatus, DateModified)
SELECT     JobIdentity, JobStatus, convert(datetime,case when (SUBSTRING(DateModified,23,1)='-') then left(DateModified,22)+'0' when (SUBSTRING(DateModified,22,1)='-') then left(DateModified,21)+'00' else left(DateModified,23) end,126)
FROM OPENXML (@idoc, '/NewDataSet/tblSMTPJobs', 2) 
WITH (
    [JobIdentity] uniqueidentifier,
    [JobStatus] int,
    [DateModified] char(33)
)

IF @@rowcount > 0
BEGIN
    UPDATE [dbo].[EmailJob_Recipient]
    SET DSNed = 0, RecentDSNDate = NULL, RecentDSNSignatureID = NULL, RecentDSNCategory = NULL, StatusReport = NULL, Sent = 0, SentDate = NULL
    WHERE [EmailJobID] IN (
        SELECT ID
        FROM EmailJob j
        INNER JOIN #Temp sj
        ON j.WSSMTPJobIdentifier = sj.JobIdentity
        WHERE j.WSDateLastAccessed <> sj.DateModified and j.status IN (7,14)
        )

    UPDATE [dbo].EmailJob
    SET
    [Status] =
    CASE JobStatus
    WHEN 1 THEN 4
    WHEN 2 THEN 6
    WHEN 3 THEN 7
    WHEN 4 THEN 14
    WHEN 5 THEN 15
    WHEN 6 THEN 15
    WHEN 7 THEN 6
    WHEN 8 THEN 6
    WHEN 9 THEN 14
    END,
    [WSDateLastAccessed] = sj.DateModified
    FROM [dbo].EmailJob AS j
    INNER JOIN #Temp sj
    ON j.WSSMTPJobIdentifier = sj.JobIdentity

    ----------------------------------
    --Update Conditional Parent Jobs--
    ----------------------------------
        -- If the parent job is at status 4, it hasn't been updated yet.
        -- First we will set it to complete if any of its jobs have just been completed

        Declare @ParentJobs Table (ID int)

        INSERT INTO @ParentJobs
        SELECT parentJob.ID
        FROM [dbo].[EmailJob] as parentJob
        INNER JOIN [dbo].[EmailJob] as childJob
        ON childJob.ConditionalParentJobID = parentJob.ID    
        INNER JOIN #Temp sj
        ON childJob.WSSMTPJobIdentifier = sj.JobIdentity
        WHERE sj.JobStatus = 3
        AND parentJob.[Status] = 4

        UPDATE [dbo].[EmailJob]
        SET [STATUS] = 7
        FROM EmailJob j
        INNER JOIN @ParentJobs pj
        on j.ID = pj.ID

        DELETE FROM @ParentJobs

        -- Now we will set it to exception if none of its jobs are completed yet but some have an exception
        INSERT INTO @ParentJobs
        SELECT parentJob.ID
        FROM [dbo].[EmailJob] as parentJob
        INNER JOIN [dbo].[EmailJob] as childJob
        ON childJob.ConditionalParentJobID = parentJob.ID    
        INNER JOIN #Temp sj
        ON childJob.WSSMTPJobIdentifier = sj.JobIdentity
        WHERE sj.JobStatus = 4
        AND parentJob.[Status] = 4

        UPDATE [dbo].[EmailJob]
        SET [STATUS] = 14
        FROM EmailJob j
        INNER JOIN @ParentJobs pj
        on j.ID = pj.ID

        DELETE FROM @ParentJobs

        --Now we need to move it to Partial success if it already has an exception
        --and something has been completed OR if it already has a completion and something has an exception
        INSERT INTO @ParentJobs
        SELECT parentJob.ID
        FROM [dbo].[EmailJob] as parentJob
        INNER JOIN [dbo].[EmailJob] as childJob
        ON childJob.ConditionalParentJobID = parentJob.ID    
        INNER JOIN #Temp sj
        ON childJob.WSSMTPJobIdentifier = sj.JobIdentity
        WHERE (sj.JobStatus = 4 AND parentJob.[Status] = 7) OR (sj.JobStatus = 3 AND parentJob.[Status] = 14)

        UPDATE [dbo].[EmailJob]
        SET [STATUS] = 17
        FROM EmailJob j
        INNER JOIN @ParentJobs pj
        on j.ID = pj.ID

    UPDATE [dbo].EmailJob_Recipient
    SET
    Sent = 1,
    SentDate = WSDateLastAccessed,
    UpdateDate = GETUTCDATE()
    FROM [dbo].EmailJob_Recipient r
    INNER JOIN [dbo].EmailJob j on j.ID = r.EmailJobID
    INNER JOIN #Temp sj on sj.JobIdentity = j.WSSMTPJobIdentifier
    WHERE sj.JobStatus = 3

    DECLARE @UpdateDate datetime
    SELECT @UpdateDate = MAX(DateModified)
    FROM #Temp

    UPDATE [dbo].[ServerStatus]
    SET UpdateDate = @UpdateDate
    WHERE ID = 1

    if @@rowcount = 0
        insert into dbo.ServerStatus(ID, UpdateDate) values (1, @UpdateDate)
END

EXEC sp_xml_removedocument @idoc

DROP TABLE #Temp